oracle 按指定字符拆分字符串（split）

1.情景展示

在oracle当中，如何拆分以逗号隔开的字符串？（将字符串按指定字符拆分成多行数据）

比如：411600,411602,411603,411606,411609,411621,411622,411623,411624,411625,411626,411627,411628,411643,411681。

2.具体分析

oracle当中没有专门的字符串拆分函数；

但是，我们可以通过REGEXP_SUBSTR()和CONNECT BY LEVEL来实现。

如果，需要自定义创建split函数的话，见文末推荐。

3.解决方案

语法：

SELECT REGEXP_SUBSTR(string,
                     '[^特定字符]+',
                     1,
                     LEVEL,
                     'i') as 分割后结果
  FROM DUAL
CONNECT BY LEVEL <= 特定字符在字符串当中出现的次数 + 1;

进一步优化

SELECT REGEXP_SUBSTR(string,
                     '[^特定字符]+',
                     1,
                     LEVEL,
                     'i') as 起个别名
  FROM DUAL
CONNECT BY LEVEL <= LENGTHB(TRANSLATE(string, '特定字符' || string, '特定字符')) + 1;

查看代码 

SELECT REGEXP_SUBSTR('411600,411602,411603,411606,411609,411621,411622,411623,411624,411625,411626,411627,411628,411643,411681',
                     '[^,]+',
                     1,
                     LEVEL,
                     'i') as 分割后结果
  FROM DUAL
CONNECT BY LEVEL <= LENGTHB(TRANSLATE('411600,411602,411603,411606,411609,411621,411622,411623,411624,411625,411626,411627,411628,411643,411681', ',' || '411600,411602,411603,411606,411609,411621,411622,411623,411624,411625,411626,411627,411628,411643,411681', ',')) + 1;
SELECT REGEXP_SUBSTR('411600,411602,411603,411606,411609,411621,411622,411623,411624,411625,411626,411627,411628,411643,411681',
                     '[^,]+',
                     1,
                     LEVEL,
                     'i') as 分割后结果
  FROM DUAL
CONNECT BY LEVEL <= LENGTHB(TRANSLATE('411600,411602,411603,411606,411609,411621,411622,411623,411624,411625,411626,411627,411628,411643,411681',
                                      ',' ||
                                      '411600,411602,411603,411606,411609,411621,411622,411623,411624,411625,411626,411627,411628,411643,411681',
                                      ',')) + 1;

 

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

posted @ 2022-03-14 17:56  Marydon  阅读(16051)  评论(0)  编辑  收藏  举报