工作中,我们经常会碰到列转行的情况
with a as (select 'ABC,AA,AD,ABD,JI,CC,ALSKD,ALDKDJ' id from dual)
select regexp_substr(id,'[^,]+',1,rownum) id from a
connect by rownum <= length(regexp_replace(id,'[^,]+'))
转换后结果:
问题
一看分割后少了一行,是因为正则只匹配到 7个 逗号,后面手动 加上 1 就可以了
with a as (select 'ABC,AA,AD,ABD,JI,CC,ALSKD,ALDKDJ' id from dual)
select regexp_substr(id,'[^,]+',1,rownum) id from a
connect by rownum <= length(regexp_replace(id,'[^,]+'))+1