把一行里 ‘，’ 隔开的数据一行转换成多行
一个需求：
需要把一行里 ‘，’ 隔开的数据一行转换成多行：
转换成：
sql 如下：
select id,
nvl(substr(mm,instr(mm,',',1,lev)+1,instr(mm,',',1,lev+1)-instr(mm,',',1,lev)-1),' ') mm
from (
select id, lev, ',' || mm || ',' mm
from (
Select 1 Id, '张三李四' mm From dual
Union All
Select 2 Id, '张三,李四' mm From dual
Union All
Select 3 Id, '张,三李四' mm From dual
Union All
Select 4 Id, '张三李,四' mm From dual
Union All
Select 5 Id, '张三李四,' mm From dual
) a,(select level lev
from dual
connect by level <=
(select max(length(mm) - length(replace(mm, ','))) + 1
from (
Select 1 Id, '张三李四' mm From dual
Union All
Select 2 Id, '张三,李四' mm From dual
Union All
Select 3 Id, '张,三李四' mm From dual
Union All
Select 4 Id, '张三李,四' mm From dual
Union All
Select 5 Id, '张三李四,' mm From dual
))) b)
where substr(mm,instr(mm,',',1,lev)+1,instr(mm,',',1,lev+1)-instr(mm,',',1,lev)-1) is not Null
Order By Id ；