with test as ( select 'aaa' a,'1,2,3' c from dual union all select 'bbb' a,'1,2' c from dual )
select a,substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) -(instr(t.ca, ',', 1, c.lv) + 1)) AS c from (select a,',' || c || ',' AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test)t, (select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt order by a a c --------------------- 1 aaa 1 2 aaa 2 3 aaa 3 4 bbb 2 5 bbb 1
with t as( select 'a,b,c,d' str from dual ) SELECT regexp_substr(str, '[^,]+', 1, level) from t connect by level <= length(str) - length(replace(str, ',', '')) + 1; REGEXP_SUBSTR(STR,'[^,]+',1,LE ------------------------------ a b c d
select regexp_substr(values, '[^,]+',1,levle) from t connect by level <= length(values) - length(replace(values,',','')) + 1
with test as
(
select 'aaa' a,'1,2,3' c from dual union all
select 'bbb' a,'1,2' c from dual
)
select a,substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) -(instr(t.ca, ',', 1, c.lv) + 1)) AS c
from (select a,',' || c || ',' AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test)t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt
order by a a c
---------------------
1 aaa 1
2 aaa 2
3 aaa 3
4 bbb 2
5 bbb 1
select 'a,b,c,d' str from dual
)
SELECT regexp_substr(str, '[^,]+', 1, level)
from t
connect by level <= length(str) - length(replace(str, ',', '')) + 1;
REGEXP_SUBSTR(STR,'[^,]+',1,LE
------------------------------
a
b
c
d
from t
connect by level <= length(values) - length(replace(values,',','')) + 1