CODEWITHSUNDEEP
sqlregexoracle-database
Lazar Lazarov
Lazar Lazarov

Reputation: 2532

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.

Upvotes: 0

Views: 1978

Answers (3)

user5683823
user5683823

Reputation:

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.

Upvotes: 0

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

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

Upvotes: 1

nilsman
nilsman

Reputation: 396

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) );

Upvotes: 0

Related Questions