关闭

oracle sql 将字符串转换成多行

235人阅读 评论(0) 收藏 举报
分类:

    今天遇到客户要求将字符串转换成多行的形式,自己摸索了一些;大家直接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;



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:40285次
    • 积分:1087
    • 等级:
    • 排名:千里之外
    • 原创:67篇
    • 转载:11篇
    • 译文:0篇
    • 评论:3条
    最新评论