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 workgroupsfrom dualunionselect '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 groupcountFROM tmp_t) a,(select LEVEL lv from dual CONNECT BY LEVEL <= 5) bwhere b.lv <= a.groupcountorder 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_signlefrom 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;