Split string in Oracle DB

Question

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)

psaraj12 · Accepted Answer · 2014-12-22T10:18:02.880

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;

Thank you. Your solution is good for comma-separated example. (as in my question SQL ), but I need `newline` separated example. — Tony, Dec 22 '14 at 09:41
The another question that is using your answer is in http://stackoverflow.com/q/27602170/2023524. — Tony, Dec 22 '14 at 12:11
may be this might help your other question http://stackoverflow.com/questions/25524281/oracle-how-to-create-a-function-returning-values-for-a-select-from-tab-where/25528438#25528438 — psaraj12, Dec 22 '14 at 12:13

score 0 · Answer 2 · answered Dec 22 '14 at 08:45

0

Use substr to return the first character:

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

answered Dec 22 '14 at 08:45

road242

2,492
22
30

thank you for your answer. But the name of first word can contain more than 1 character. I've updated my question. – Tony Dec 22 '14 at 08:48

Split string in Oracle DB

2 Answers2

Linked