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-22 10:18:02Z

1

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;

edited Dec 22, 2014 at 10:18

answered Dec 22, 2014 at 9:13

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tony Over a year ago

Thank you. Your solution is good for comma-separated example. (as in my question SQL ), but I need newline separated example.

Tony Over a year ago

The another question that is using your answer is in stackoverflow.com/q/27602170/2023524.

psaraj12 Over a year ago

may be this might help your other question stackoverflow.com/questions/25524281/…

road242 · Accepted Answer · 2014-12-22 08:45:15Z

0

Use substr to return the first character:

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

answered Dec 22, 2014 at 8:45

road242

2,5321 gold badge22 silver badges31 bronze badges

1 Comment

Tony Over a year ago

thank you for your answer. But the name of first word can contain more than 1 character. I've updated my question.

Collectives™ on Stack Overflow

Split string in Oracle DB

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related