oracle REGEXP_SUBSTR函数

转载摘抄：https://blog.csdn.net/a720561252803/article/details/90702542
https://blog.csdn.net/zz87250976/article/details/22856829

最近做了一个业务需要将表中的像这样的 “1,2,3,4,6”
像这样的字符串转换成list形式

因为业务需求需要查询条件 in 里面的值 但是这样没办法in
所以，上代码：

SELECT
 REPLACE (
  REGEXP_SUBSTR (IDS, '[^,]+', 1, lv), --转换成的数组    REPORTX_IDS 要转换的数据字段
  ' ',
  NULL
 ) IDS
FROM
 REPORT,   --表名
 (
  SELECT
   LEVEL lv
  FROM
   dual CONNECT BY LEVEL < 100 --1到100的集合
 ) oo
WHERE
 oo.lv <= REGEXP_COUNT (IDS, '\,') + 1  -- REPORTX_IDS 为'1,2,3,4,5'的字符串 要分开的字段    -- REGEXP_COUNT (REPORTX_IDS, '\,') 计算 REPORTX_IDS 字段所有,的个数
AND TID = 3

查询tid = 3 的执行结果：

Oracle中如何使用REGEXP_SUBSTR函数

REGEXP_SUBSTR函数格式如下：
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr ：需要进行正则处理的字符串
__pattern ：进行匹配的正则表达式
__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）
__occurrence ：标识第几个匹配组，默认为1
__modifier ：模式（'i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’。）

实际应用如下：在oracle中，使用一条语句实现将’34,56,-23’拆分成’34’，‘56’，’-23’的集合。

–1、查询使用正则分割后的第一个值，也就是34 SELECT REGEXP_SUBSTR(‘34,56,-23’,’[^,]+’,1,1,‘i’) AS STR FROM DUAL;
–结果是：34
–2、查询使用正则分割后的最后一个值，也就是-23 SELECT REGEXP_SUBSTR(‘34,56,-23’,’[^,]+’,1,3,‘i’) AS STR FROM DUAL;
–结果是：-23
–3、获取一个多个数值的列，从而能够让结果以多行的形式展示出来 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;
–结果是：
–1
–2
–3
–4
–5
–6
–7
–4、将上面REGEXP_SUBSTR的occurrence关联 SELECT NVL(REGEXP_SUBSTR(‘34,56,-23’, ‘[^,]+’, 1, LEVEL, ‘i’), ‘NULLL’) AS
STR FROM DUAL CONNECT BY LEVEL <= 7;
–结果是：
–34
–56
—23
–null
–null
–mull
–null
–5、优化上面的SQL语句，让生成的行的数量符合实际情况 SELECT REGEXP_SUBSTR(‘34,56,-23’, ‘[^,]+’, 1, LEVEL, ‘i’) AS STR FROM DUAL CONNECT BY LEVEL <=
LENGTH(‘34,56,-23’) - LENGTH(REGEXP_REPLACE(‘34,56,-23’, ‘,’, ‘’)) +
1;
–结果是：
–34
–56
—23

一、REGEXP_COUNT ( source_char, pattern , position , match_param)

统计字段中包含的个数【可以是：数字，文字，英文，符号】的字符串

四个参数解释：

source:

需要用来进行分析的字符串，如  "aaaa","welcome","abc12cdg"  等

pattern:

字符（可以是正常字母数字等 a,1 等，也可以是正则表达式 /d /w  [a-z]等），整个函数的作用就是返回pattern在source中出现的次数值。

position:

从字符串的那个位置开始分析，可以省略，默认值是1

match_param:

‘i’ 用于不区分大小写的匹配
‘c’ 用于区分大小写的匹配
‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数，则句点将不匹配换行符
‘m’ 将源串视为多行。即Oracle 将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或			  结束。如果省略该参数，则Oracle将源串看作一行。
‘x’ 忽略空格字符。默认情况下，空格字符与自身相匹配。

举例：

select REGEXP_COUNT('welcome','\w')   from DUAL;

二、REGEXP_REPLACE(VARCHAR str, VARCHAR pattern, VARCHAR replacement)

三个参数的解释：

str:

指定用来分析的字符串

pattern:

被替换的字符串，其中可以是 a,b,c之类的普通字符，也可以是正则表达式 [a-z]  /w   /d等

replacement:

用于替换的字符串

举例

select regexp_replace('welcome','e','vv') from dual;
select regexp_replace('welcome','[a-z]','vvv') as result from dual;