0

Hi I know how to convert a,b,c,d,e into

a
b
c
d
e

I have written the below query for the same.

select substr(replace('a,b,c,d,e',',',null),level,1) 
from dual 
connect by level <=length(replace('a,b,c,d,e',',',null));

but I am not able to convert aa,b,cc,d into

aa
b
cc
d

Is there anyway we can do the same when we don't know the length of characters

  • ```select REPLACE ( 'aa,b,cc,d',',', CHR(10) ) FROM DUAL;``` – Kaushik Nayak Sep 21 '18 at 12:54
  • 1
    `with temp as (select 'aa,b,cc,d,2,3,hello,test' test from dual) select trim(regexp_substr(t.test, '[^,]+', 1, levels.column_value)) as TEST from temp t, table(cast(multiset (select level from dual connect by level <= length(regexp_replace(t.test, '[^,]+')) + 1) as sys.OdciNumberList)) levels` – TheName Sep 21 '18 at 12:58
  • I need it is in Oracle –  Sep 21 '18 at 13:18

0 Answers0