oracle sql 将字符串转换成多行

    今天遇到客户要求将字符串转换成多行的形式，自己摸索了一些；大家直接copy去用吧；

     

with tt as(select zhbh as b,replace(yhbh,'，',',') as a  from ptsx_tmpjyzhxx where zhbh in(6568,13810,6529))
select  a, b from 
(
select level,b,substr(a,instr(','||a,',',1,level),instr(a||',',',',1,level)-instr(','||a,',',1,level))a
from tt
connect by level<=length(a)-length(replace(a,','))+1 ) group  by b,a

   在网上也搜索了几种，大家可以试试不错的

    

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

   最简单的这种：

with t as
 (select 'a;b;c;d;e' as str from dual)
select level,
	   t.str,
	   substr(t.str, 2 * (level - 1) + 1, 1) as str_signle
  from t
connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;

with t as
 (select 'i;am;a;test;mine' as str from dual)
select level,
  str,
  regexp_substr(t.str, '[^;]+', 1, level) str_single
  from t
connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;