oracle 中对查询出来的数据进行切割、截取等操作

时间:2022-04-19 10:17:56

oracle 中对查询出来的数据进行切割、截取等操作

最近遇到一个问题,需要把一个带有,的字符串拆分成多行。通过查询资料,这个操作需要使用以下2个关键知识:

1. REGEXP_SUBSTR函数

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

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

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

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

__position   :起始位置,从第几个字符开始正则表达式匹配(默认为1)

__occurrence :标识第几个匹配组,默认为1

__modifier   :模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'。)

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

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

执行结果:

  oracle 中对查询出来的数据进行切割、截取等操作

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

执行结果:

oracle 中对查询出来的数据进行切割、截取等操作

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

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

举个例子:

select rownum from dual connect by rownum<=7;

执行结果:

oracle 中对查询出来的数据进行切割、截取等操作

可见通过connect by可以构造连续的值

3.字符串中逗号的数量是不确定的,如果有2个逗号,需要提取的字段就是3个。为了确定有多少个需要提取的字段,使用regexp_replace函数

举个列子:

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

执行结果:

oracle 中对查询出来的数据进行切割、截取等操作

可见执行正则替换后,字符串中的,被删除了。通过原字符串长度和被替换后字符串长度相减,可以得到原字符串中的逗号数量,加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;

执行结果:
oracle 中对查询出来的数据进行切割、截取等操作