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;