Oracle 逗号字符串转成行

最新推荐文章于 2023-09-27 11:15:01 发布

阿三哥哥

最新推荐文章于 2023-09-27 11:15:01 发布

阅读量852

点赞数

分类专栏： oracle 文章标签：字符串转换

本文链接：https://blog.csdn.net/yhx119/article/details/9341933

版权

oracle 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

With test As
 (Select '1' a, '2' b, '3' c, '121,122,123' d, '201,202,203' e From dual) --1条数据
Select a,
       b,
       c,
       substr(t.cd,
              instr(t.cd, ',', 1, d.lv) + 1,
              instr(t.cd, ',', 1, d.lv + 1) -
              (instr(t.cd, ',', 1, d.lv) + 1)) As d,
       substr(t.ce,
              instr(t.ce, ',', 1, d.lv) + 1,
              instr(t.ce, ',', 1, d.lv + 1) -
              (instr(t.ce, ',', 1, d.lv) + 1)) As e
  From (Select a,
               b,
               c,
               ',' || d || ',' As cd,
               ',' || e || ',' As ce,
               length(d || ',') - nvl(length(Replace(d, ',')), 0) As cnt
          From test) t,
       (Select Level lv From dual Connect By Level <= 100) d
 Where d.lv <= t.cnt


结果
1	2	3	121	201
1	2	3	122	202
1	2	3	123	203