CODEWITHSUNDEEP
sqloracle-databasesplit
Tony
Tony

Reputation: 2406

Split string in Oracle DB

I have table with 3 rows in example:

|-Id-||--props--|
1        aa = black
2        aa = change
         d = eexplore
3        xxx = yield
         d = int
         mmmm = no

I want to write SQL statement with results: 

aa    
d
xxx
mmm

I.e. output all first elements in pairs x=y. The pairs in each row of given table could be separated by newline.

I've tried the following SQL but the results are doesn't correct

    SELECT REGEXP_SUBSTR ('a = b, b = c',
                          '[^=]',
                          1,
                          LEVEL) as name
      FROM DUAL
CONNECT BY LEVEL <= (SELECT REGEXP_COUNT ('a = b, b = c', '=') FROM DUAL)

Upvotes: 1

Views: 583

Answers (2)

psaraj12
psaraj12

Reputation: 5072

Kindly try the below,the sql fiddle here

with names as ( SELECT REGEXP_SUBSTR (regexp_replace('aa = b, bb = c , eee = d','[[:space:]]*',''), '[^=]+', 1, level ) as name FROM DUAL CONNECT BY LEVEL <= (SELECT REGEXP_COUNT ('a = b, b = c , e = d ', '=') FROM DUAL)) select nvl(regexp_substr(name,'[^,]+',1,2),regexp_substr(name,'[^,]+',1,1) ) from names;

Update1:-You can replace the new line with comma

On Unix, LINEFEED (chr(10)) is the "end of line" marker. On windows CARRIAGE RETURN/LINEFEED (chr(13)||chr(10)) should be the end of line marker

with names as
(
 SELECT REGEXP_SUBSTR (regexp_replace(replace('aa = black' ||chr(13)||chr(10)||'bb = change'||chr(13)||chr(10)||'mmmm=no',chr(13)||chr(10),','),'[[:space:]]*',''),
                          '[^=]+',
                          1,
                         level ) as name
      FROM DUAL
CONNECT BY LEVEL <= (SELECT REGEXP_COUNT 
('aa = black' ||chr(13)||chr(10)||'bb = change'||chr(13)||chr(10)||'mmmm=no', '=')
 FROM DUAL)

)
select nvl(regexp_substr(name,'[^,]+',1,2),
regexp_substr(name,'[^,]+',1) ) from names;

Upvotes: 1

road242
road242

Reputation: 2532

Use substr to return the first character:

select substr(LEVEL, 1, 1) from tablename group by substr(LEVEL, 1, 1)

Upvotes: 0

Related Questions