Oracle split text into multiple rows

Question

Inside a varchar2 column I have text values like :

aaaaaa. fgdfg.
bbbbbbbbbbbbbb ccccccccc
dddddd ddd dddddddddddd,
asdasdasdll
sssss

if i do select column from table where id=... i get the whole text in a single row, normally. But i would like to get the result in multiple rows, 5 for the example above. I have to use just one select statement, and the delimiters will be new line or carriage return (chr(10), chr(13) in oracle)

Thank you!

score 3 · Accepted Answer · edited May 23 '17 at 11:51

3

Like this, maybe (but it all depends on the version of oracle you are using):

WITH yourtable AS    (SELECT REPLACE('aaaaaa. fgdfg.' ||chr(10)||           
                        'bbbbbbbbbbbbbb ccccccccc  ' ||chr(13)|| 
                        'dddddd ddd dddddddddddd,' ||chr(10)||
                        'asdasdasdll  ' ||chr(13)||
                        'sssss '||chr(10),chr(13),chr(10)) AS astr FROM DUAL)
SELECT REGEXP_SUBSTR ( astr, '[^' ||chr(10)||']+', 1, LEVEL) data   FROM yourtable   
CONNECT BY LEVEL <= LENGTH(astr) - LENGTH(REPLACE(astr, chr(10))) + 1

see: Comma Separated values in Oracle

edited May 23 '17 at 11:51

Community

1
1

answered Nov 03 '11 at 08:23

Kevin Burton

11,676
2
24
37

Thanks for the reply. If i run your example i get the five rows + 6 more wich are null. If i run on my actual test data i get only the first line.i'm using oracle 10g – maephisto Nov 03 '11 at 09:22
i think the problem is in the separators you used. the separator should be chr(10) OR chr(13), not both. – maephisto Nov 03 '11 at 09:24
just replace one of the chanracters in the string so you are looking for a single delimiter.... – Kevin Burton Nov 03 '11 at 10:48
This answer will break down if there is an empty line somewhere in your text. Reproduce: select REGEXP_SUBSTR ( 'line 1' ||chr(10)||chr(10)||'line 2', '[^' ||chr(10)||']+', 1, 2) from dual; -- This returns line 2 instead of NULL. Check here for a solution: https://stackoverflow.com/questions/25648653/regex-to-select-nth-value-from-a-list-allowing-for-nulls/25652018#25652018 – Wouter Aug 14 '15 at 12:54

score 0 · Answer 2 · answered Aug 14 '15 at 12:59

The answer by Kevin Burton contains a bug if your data contains empty lines. The adaptation below, based on the solution invented here, works. Check that post for an explanation on the issue and the solution.

WITH yourtable AS    (SELECT REPLACE('aaaaaa. fgdfg.' ||chr(10)||           
                    'bbbbbbbbbbbbbb ccccccccc  ' ||chr(13)|| 
                    chr(13)||
                    'dddddd ddd dddddddddddd,' ||chr(10)||
                    'asdasdasdll  ' ||chr(13)||
                    'sssss '||chr(10),chr(13),chr(10)) AS astr FROM DUAL)
SELECT REGEXP_SUBSTR ( astr, '([^' ||chr(10)||']*)('||chr(10)||'|$)', 1,  LEVEL, null, 1) data   FROM yourtable   
CONNECT BY LEVEL <= LENGTH(astr) - LENGTH(REPLACE(astr, chr(10))) + 1;

Oracle split text into multiple rows

2 Answers2