oracle的split string
sqlcode
with tt as( select 1 id,'aaa,bb,ccc' str from dual
union all
select 2 id,'ddd,eee,fff,gggggg' str from dual
union all
select 3 id,'hhh' str from dual
union all
select 4 id,'iiii,gggggg,kkkkkk,llll,mmmm,nn,ooo' str from dual
union all
select 5 id,'ppp,qqq' str from dual)
SELECT id,res FROM
(SELECT id, str, regexp_substr(str, '[^,]+', 1, LEVEL) res, LEVEL lv, lag(LEVEL) over(PARTITION BY id ORDER BY LEVEL) lg FROM tt t CONNECT BY regexp_substr(str, '[^,]+', 1, LEVEL) IS NOT NULL)
WHERE lv != lg or lg is null
with tt as( select 1 id,'aaa,bb,ccc' str from dual
union all
select 2 id,'ddd,eee,fff,gggggg' str from dual
union all
select 3 id,'hhh' str from dual
union all
select 4 id,'iiii,gggggg,kkkkkk,llll,mmmm,nn,ooo' str from dual
union all
select 5 id,'ppp,qqq' str from dual)
SELECT id,res FROM
(SELECT id, str, regexp_substr(str, '[^,]+', 1, LEVEL) res, LEVEL lv, lag(LEVEL) over(PARTITION BY id ORDER BY LEVEL) lg FROM tt t CONNECT BY regexp_substr(str, '[^,]+', 1, LEVEL) IS NOT NULL)
WHERE lv != lg or lg is null