Oracle分隔符转成行,oracle简单按分隔符单行转多行sql

今天看到篇博客，外链网址已屏蔽，说的是按分隔符把一列转成多行，原文给出的方法是：

with tmp_t as

(select '1001' as userId, '10,12,15' as workgroups

from dual

union

select '1002' as userId, '2,4,5' as workgroups from dual)

select userid,

substr(tempgroups,

instr(tempgroups, ',', 1, lv) + 1,

instr(tempgroups, ',', 1, lv + 1) -

(instr(tempgroups, ',', 1, lv) + 1))

from (select userid,

',' || workgroups || ',' AS tempgroups,

length(workgroups || ',') -

nvl(length(replace(workgroups, ',')) ， 0) AS groupcount

FROM tmp_t) a,

(select LEVEL lv from dual CONNECT BY LEVEL <= 5) b

where b.lv <= a.groupcount

order by userid, lv

结果为：

使用了CONNECT BY 构造1-5的lv值和instr截取，详细分析见原文。

个人看到connect by level <=5总想这替换掉5，只要得到表中最大的,个数就可以了，如下：

with tmp_t as

(select '1001' as userid, '10,12,15' as workgroups

from dual

union

select '1002' as userid, '2,4,5' as workgroups from dual)

select userid,

substr(tempgroups,

instr(tempgroups, ',', 1, lv) + 1,

instr(tempgroups, ',', 1, lv + 1) -

(instr(tempgroups, ',', 1, lv) + 1))

from (select userid,

',' || workgroups || ',' as tempgroups,

length(workgroups || ',') -

nvl(length(replace(workgroups, ',')) ， 0) as groupcount

from tmp_t) a,

(select level lv

from dual

connect by level <= (select max(length(workgroups) -

length(replace(workgroups, ',', ''))) + 1

from tmp_t)) b

where b.lv <= a.groupcount

order by userid, lv

上面的写法简单易懂，恰巧昨天看到篇文章，里面有更好的写法，原文链接：外链网址已屏蔽

更好的写法如下：

with tmp_t as

(select '1001' as userid, '10,12,15' as workgroups

from dual

union

select '1002' as userid, '2,4,5' as workgroups from dual)

select userid,

--regexp_substr(str,reg,起始位置 第几次)

regexp_substr(workgroups, '[^,]+', 1, level) clr

from tmp_t

connect by level <= regexp_count(workgroups, ',') + 1

--regexp_count(workgroups, ',') 统计字符串中,的数量

--用法参考外链网址已屏蔽

and userid = prior userid

and prior dbms_random.value is not null

相当简洁，很不错的写法，结果和上面的一样。

全文完。