select a,b,regexp_substr(c,'[^,]+',1,n) c from
table t,(select level n from dual connect by level<=10) extend_rows
where regexp_substr(c,'[^,]+',1,n) is not null
table t,(select level n from dual connect by level<=10) extend_rows
where regexp_substr(c,'[^,]+',1,n) is not null
with t1 as
(
select 'a,b,c' c1 from dual
)select distinct regexp_substr(c1, '[^,]+',1,level) c1
from t1
connect by level <= length(c1) - length(replace(c1,',','')) + 1 c1
----------------------------
1 a
2 b
3 c
t1
connect by level <= length(c) - length(replace(c,',','')) + 1
select distinct a,b,regexp_substr(c,'[^,]+',1,level,'i') c from
t1
connect by level <= length(c) - length(replace(c,',','')) + 1
你这写法,如果他A,B,C,这样的值。就有为空值了
with t1 as
(
select 1 id,'a,b,c' c1 from dual union all
select 2,'d,e,' c1 from dual
)
select distinct id,
regexp_substr(decode(substr(c1,-1),',',substr(c1,1,length(c1)-1),c1), '[^,]+',1,level) c1
from t1
connect by level <= length(decode(substr(c1,-1),',',substr(c1,1,length(c1)-1),c1)) - length(replace(c1,',','')) + 1 id c1
------------------------
1 1 a
2 1 c
3 1 b
4 2 d
5 2 e
regexp_substr(b, '[^,]+',1,level) as b
from t_test
connect by level <= length(decode(substr(b,-1),',',substr(b,1,length(b)-1),b)) - length(replace(b,',',''))+1
order by a;
结果: