Oracle 以逗号分隔的字符串拆分为多行数据实例详解

2022 年 2 月 19 日 1684 点热度 1 人点赞 0 条评论

前言

近期在工作中遇到某表某字段是可扩展数据内容，信息以逗号分隔生成的，现需求要根据此字段数据在其它表查询相关的内容展现出来，第一想法是切割数据，以逗号作为切割符，以下为总结的实现方法，以供大家参考、指教。

1 、 regexp_substr 函数，通过正则来拆分字符串，函数用法为:(必须是 oracle 10g+的版本才支持）

REGEXP_SUBSTR 函数格式如下：

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr ：需要进行正则处理的字符串

__pattern ：进行匹配的正则表达式

__position ：起始位置，从第几个字符开始正则表达式匹配（默认为 1）

__occurrence ：获取第几个分割出来的组（分割后最初的字符串会按分割的顺序排列成组），默认为 1

__modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）针对的是正则表达式里字符大小写的匹配

此函数只能每次取一个字符串出来，有点鸡肋，字符串中逗号的数量是不确定的，如果有 2 个逗号，需要提取的字段就是 3 个。为了确定有多少个需要提取的字段，需要用到 connect by 命令实现动态参数构造连续的值，通过原字符串长度和被替换后字符串长度相减，可以得到原字符串中的逗号数量，加 1 后得到需要提取的匹配字段数量。

SQL:

select bs from cs1_0 where slid='201804100038'
--正则分割后的第一个值
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,1,'i') as 分割后结果 FROM DUAL;
--获取一个多个数值的列，从而能够让结果以多行的形式展示出来 
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=5;
--将上面 REGEXP_SUBSTR 的 occurrence（标识第几个匹配组） 实现动态参数，使用 connect by 组合起来
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,LEVEL,'i') as 分割后结果 FROM DUAL CONNECT BY LEVEL <=5;
--优化一下 （动态获匹配组标识行数）
select regexp_substr((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,LEVEL,'i') as 分割后结果 from dual 
connect by level <= length((select bs from cs1_0 where slid='201804100038'))-length(regexp_replace((select bs from cs1_0 where slid='201804100038'),',',''))+1;

2 、以 Type 类型和 function 函数的方式实现

1） 建立 TYPE 类型


CREATE OR REPLACE TYPE strsplit_type_12 IS TABLE OF VARCHAR2 (4000)

2） 建立 function 存储函数
create or replace function strsplit_66(p_value varchar2,p_split varchar2 )  --字符串，切割符
--根据特定字符来切割字符串
return strsplit_type_12
pipelined is
v_idx       integer;
v_str       varchar2(500);
v_strs_last varchar2(4000) := p_value;

begin
loop
  v_idx := instr(v_strs_last, p_split);
  exit when v_idx = 0;
  v_str       := substr(v_strs_last, 1, v_idx - 1);
  v_strs_last := substr(v_strs_last, v_idx + 1);
  pipe row(v_str);
end loop;
pipe row(v_strs_last);
return;
end strsplit_66;

SELECT ROWNUM 序号, a.* FROM TABLE(strsplit_66((select bs from cs1_0 where slid='201804100038'), ',')) a;

测试一下：