Split by uppercase Oracle

Question

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.

You will find your answer here. http://stackoverflow.com/questions/23470794/split-words-with-a-capital-letter-in-sql — Marc Giroux, Jul 28 '16 at 12:22

Frank Ockenfuss · Accepted Answer · 2016-07-28T13:15:22.090

1

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

edited Jul 28 '16 at 13:15

answered Jul 28 '16 at 12:44

Frank Ockenfuss

2,023
11
26

Great. Now if you can extend your solution to : "Insert a blank between each lower case character followed by an upper case character and make those lower case characters upper and also the first character of the row" will be everything I am looking for – Lazar Lazarov Jul 28 '16 at 12:50
Yes we now have upper first char which is great ! 'isThisAnExample' --> 'Is This An Example'. Now the last request is from 'Is This An Example' --> 'Is this an example'. – Lazar Lazarov Jul 28 '16 at 13:01
Use initcap(lower('Is This An Example')). – nilsman Jul 28 '16 at 13:10
2

@nilsman initcap capitalizes each word, this is not what lazarov asked for. See my changes – Frank Ockenfuss Jul 28 '16 at 13:17

score 0 · Answer 2 · answered Jul 28 '16 at 12:50

Better use regex, but anyway:

SELECT listagg(splitted, '') within GROUP (ORDER BY lvl) FROM( 
    SELECT LEVEL lvl, CASE WHEN SUBSTR(your_string, LEVEL, 1) = 
                    UPPER(SUBSTR(your_string, LEVEL, 1)) 
        THEN ' ' || SUBSTR(your_string, LEVEL, 1) ELSE 
                    SUBSTR(your_string, LEVEL, 1)  END splitted
FROM (SELECT 'isThisAnExample' your_string FROM dual) 
CONNECT BY LEVEL <= LENGTH(your_string) );

score 0 · Answer 3 · answered Jul 28 '16 at 13:37

Similar to Frank's solution, but simpler (reducing the use of regular expressions as much as possible):

with
     input ( str ) as (
       select 'isThisAnExample' from dual
     )
select upper(substr(str, 1, 1)) ||
       lower(regexp_replace(substr(str, 2), '(^|[[:lower:]])([[:upper:]])', '\1 \2'))
          as modified_str
from   input;

MODIFIED_STR
------------------
Is this an example

1 row selected.

Split by uppercase Oracle

3 Answers3