0

How to split from single row to multiple rows? There is a new line break in the ColumnC.

enter image description here

So if click edit I'll get this: enter image description here

Finally got it used this below: select it.ColumnA, it.ColumnB, REGEXP_SUBSTR((rtrim(translate(Lower(it.ColumnC), chr(10)||chr(11)||chr(13), '$'), '$')),'[^$]+', 1, LEVEL) from myTable it CONNECT BY REGEXP_SUBSTR((rtrim(translate(Lower(it.ColumnC), chr(10)||chr(11)||chr(13), '$'), '$')),'[^$]+', 1, LEVEL) IS NOT NULL GROUP BY it.ColumnA, it.ColumnB, REGEXP_SUBSTR((rtrim(translate(Lower(it.ColumnC), chr(10)||chr(11)||chr(13), '$'), '$')),'[^$]+', 1, LEVEL)

Yurker
  • 77
  • 1
  • 4
  • Even [more](http://www.orafaq.com/node/2290), and [more](https://lalitkumarb.wordpress.com/2014/12/02/split-comma-delimited-string-into-rows-in-oracle/) – Nick Krasnov Sep 29 '17 at 15:56
  • It is not duplicated question. All the link do not answer the question. – Yurker Oct 03 '17 at 15:00
  • Finally got it used this below: select it.ColumnA, it.ColumnB, REGEXP_SUBSTR((rtrim(translate(Lower(it.ColumnC), chr(10)||chr(11)||chr(13), '$'), '$')),'[^$]+', 1, LEVEL) from myTable it CONNECT BY REGEXP_SUBSTR((rtrim(translate(Lower(it.ColumnC), chr(10)||chr(11)||chr(13), '$'), '$')),'[^$]+', 1, LEVEL) IS NOT NULL GROUP BY it.ColumnA, it.ColumnB, REGEXP_SUBSTR((rtrim(translate(Lower(it.ColumnC), chr(10)||chr(11)||chr(13), '$'), '$')),'[^$]+', 1, LEVEL) – Yurker Oct 03 '17 at 15:20
  • Your solution does not handle NULL list elements. Try deleting an element while keep the carriage return. It will return 2 rows instead of 3 as expected. Try this: `with myTable(ColumnA, ColumnB, ColumnC) as ( select 1, 'king', 'Dog Cat Dog,cat' from dual ) select it.ColumnA, it.ColumnB, lower(REGEXP_SUBSTR(it.ColumnC, '(.*?)('||chr(10)||'|$)', 1, LEVEL) ) ColumnC from myTable it CONNECT BY level <= REGEXP_COUNT(it.ColumnC, chr(10))+1 GROUP BY it.ColumnA, it.ColumnB, REGEXP_SUBSTR(it.ColumnC, '(.*?)('||chr(10)||'|$)', 1, LEVEL) order by ColumnC;` – Gary_W Oct 03 '17 at 21:20

0 Answers0