oracle sql 逗号 分割 REGEXP_SUBSTR 函数

1.REGEXP_SUBSTR函数.

这个函数的作用是正则分隔字符串,用法为

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

参数说明:
参数1:string-待分割字符串;
参数2:pattern-正则表达式;
参数3:position-起始位置,从第几个字符开始正则表达式匹配(默认为1);
参数4:occurrence-标识第几个匹配组,默认为1;

参数5:modifier-模式('i'不区分大小写进行检索,'c'区分大小写进行检索,默认为'c'.);

1.分割字符串中所有的逗号,然后成多行.

select regexp_substr(q.nums, '[^,]+', 1, rownum,'i'), names

from (select '1,2,3' nums, '张三' names from dual) q
connect by rownum <= LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1;

执行结果:

1.my sql code:

SELECT test.AUDITED_UNIT, REGEXP_SUBSTR(test.AUDITED_UNIT, '[^,]+', 1, ROWNUM)
FROM (SELECT AUDITED_UNIT FROM AUDIT_PROJECT_APPROVED WHERE APPROVED_ID='3345463') test
CONNECT BY ROWNUM<=LENGTH(test.AUDITED_UNIT)-LENGTH(regexp_replace(test.AUDITED_UNIT, ',', ''))+1;

执行结果:

1.举几个例子说明下这个函数的用法:

select regexp_substr('1,2,3','[^,]+',1,1) result from dual;

执行结果:

select regexp_substr('1,2,3','[^,]+',1,2) result from dual;

执行结果:

可见occurrence参数用来指定要提取第几个匹配到的数据.以字符串'1,2,3'为例,这个参数分别要为1,2,3.

2.为了实现动态参数,使用 connect by

举个例子:

select rownum from dual connect by rownum<=7;

执行结果:

3.字符串中逗号的数量是不确定的,如果有2个逗号,需要提取的字段就是3个.

为了确定有多少个需要提取的字段,使用regexp_replace函数.

举个列子:

select regexp_replace('1,2,3',',','') from dual;

执行结果:

 

4.可见执行正则替换后,字符串中的','被删除了.通过原字符串长度和被替换后字符串长度相减,可以得到原字符串中的逗号数量,加1后得到需要提取的匹配(被逗号分割的)字段数量.

最终的语句为：

SELECT REGEXP_SUBSTR ('1,2,3', '[^,]+', 1,rownum)

from dual

connect by rownum<=LENGTH ('1,2,3') - LENGTH (regexp_replace('1,2,3', ',', ''))+1;

执行结果: