lazarov lazarov - 4 months ago 23
SQL Question

Split by uppercase Oracle

I am loooking for a regex expression or something that from this :

------------------------
| id | prop_name |
------------------------
| 1 | isThisAnExample |
------------------------


To this :

-----------------------------
| id | prop_name |
-----------------------------
| 1 | Is This An Example |
-----------------------------


Of course it would be cool if the first character is uppercase and also if the other words start with lowercase. But only spliting them also will be okay.

Answer

Maybe this is the regexp you are looking for

"Insert a blank between each lower case character followed by an upper case character":

select regexp_replace('IsThisAnExample', '([[:lower:]])([[:upper:]])', '\1 \2') from dual

First character can simply replaced by an upper case letter by

select upper(substr('isThisAn Example', 1,1))||substr('isThisAn Example', 2) from dual;

So, first replace the first character and regexp_replace for the result:

select regexp_replace(upper(substr('isThisAn Example', 1,1))||substr('isThisAn Example', 2), '([[:lower:]])([[:upper:]])', '\1 \2') from dual;

If only the first character of your sentence should be an upper case letter, then try:

select upper(substr(regexp_replace('IsThisAnExample', '([[:lower:]])([[:upper:]])', '\1 \2'),1,1))||
       lower(substr(regexp_replace('IsThisAnExample', '([[:lower:]])([[:upper:]])', '\1 \2'),2))
 from dual
Comments