Oracle 正则表达式函数 REGEXP

Oracle中的支持正则表达式的函数主要有以下五个：

1、REGEXP_LIKE ：与LIKE的功能相似，可以支持按正则表达式与文本进行匹配
2、REGEXP_INSTR ：返回指定字符串中与正则表达式匹配部分第一次出现的位置
3、REGEXP_COUNT ：返回指定字符串中与正则表达式匹配部分出现的次数
4、REGEXP_SUBSTR ：截取指定字符串中与正则表达式匹配的部分
5、REGEXP_REPLACE ：替换指定字符串中与正则表达式匹配的部分

一、REGEXP_LIKE()

REGEXP_LIKE(STRING, REGEX, MODIFIER)

STRING：需要进行正则处理的字符串
REGEX：进行匹配的正则表达式
MODIFIER：模式（‘i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’）

示例：

SELECT ENAME, JOB FROM EMP WHERE REGEXP_LIKE(JOB, '(clerk|analyst)', 'i');

结果：

二、REGEXP_INSTR()

REGEXP_INSTR(STRING, REGEX[, START_POSITION[, OCCURRENCE[, RETURN_OPTION[, MODIFIER]]]])

STRING：需要进行正则处理的字符串
REGEX：进行匹配的正则表达式
START_POSITION：起始位置，从字符串的第几个字符开始正则表达式匹配（默认从第一个字符开始，值为1）
OCCURRENCE：获取分割出来的第几组子串（分割后最初的字符串会按分割的顺序排列成数组）
RETURN_OPTION：指定返回值的类型。为0，则返回匹配值第一次出现的第一个字符的角标，非0，则返回匹配值第一次出现的最后一个字符的角标+1
MODIFIER：模式
‘c’ 区分大小写进行检索。默认为‘c’
‘i’ 不区分大小写进行检索
‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数，句点将不匹配换行符
‘m’ 将源串视为多行。即 Oracle 中将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或结束。如果省略该参数，则Oracle将源串看作一行。
‘x’ 忽略源串中的空格字符。默认情况下，空格字符与自身相匹配。
返回值：返回满足正则表达式的字符或字符串第一次出现的角标，如果没有找到结果，则返回0。

示例：

SELECT REGEXP_INSTR('11a22A33a', 'a') AS STR FROM DUAL;

结果：3

SELECT REGEXP_INSTR('11a22A33a11a22A33a', '2A', 1, 1, 0, 'c') AS STR FROM DUAL;

结果：5

SELECT REGEXP_INSTR('11a22A33a11a22A33a', '2A', 1, 1, 1, 'c') AS STR FROM DUAL;

结果：7

三、REGEXP_COUNT()

REGEXP_COUNT(STRING, REGEX[, START_POSITION[, MODIFIER]])

STRING：需要进行正则处理的字符串
REGEX：进行匹配的正则表达式
START_POSITION：起始位置，从字符串的第几个字符开始正则表达式匹配（默认从第一个字符开始，值为1）
MODIFIER：模式
‘c’ 区分大小写进行检索。默认为‘c’
‘i’ 不区分大小写进行检索
‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数，句点将不匹配换行符
‘m’ 将源串视为多行。即 Oracle 中将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或结束。如果省略该参数，则Oracle将源串看作一行。
‘x’ 忽略源串中的空格字符。默认情况下，空格字符与自身相匹配。
返回值：返回满足正则表达式的字符或字符串出现的次数。

示例：

SELECT REGEXP_COUNT('11a22A33a11a22A33a', '2A', 1, 'c') AS STR FROM DUAL;

结果：2

四、REGEXP_SUBSTR()

REGEXP_SUBSTR(STRING, REGEX[, START_POSITION[, OCCURRENCE[, MODIFIER]]])

STRING：需要进行正则处理的字符串
REGEX：进行匹配的正则表达式
START_POSITION：起始位置，从字符串的第几个字符开始正则表达式匹配（默认从第一个字符开始，值为1）
OCCURRENCE：获取分割出来的第几组子串（分割后最初的字符串会按分割的顺序排列成数组）
MODIFIER：模式
‘c’ 区分大小写进行检索。默认为‘c’
‘i’ 不区分大小写进行检索
‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数，句点将不匹配换行符
‘m’ 将源串视为多行。即 Oracle 中将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或结束。如果省略该参数，则Oracle将源串看作一行。
‘x’ 忽略源串中的空格字符。默认情况下，空格字符与自身相匹配。

示例：

SELECT REGEXP_SUBSTR('11a22A33a', '[^A]+', 1, 1, 'i') AS STR FROM DUAL;

结果：11
分析：正则表达式是以A为标识进行分割，而’i’标识不区分大小写，从第一个字符开始，取第一组截取结果，所以结果是11，而不是11a22

SELECT REGEXP_SUBSTR('11a22A33a', '[^A]+', 1, 1, 'c') AS STR FROM DUAL;

结果：11a22
分析：正则表达式是以A为标识进行分割，而’c’标识区分大小写，从第一个字符开始，取第一组截取结果，所以结果是11a22，而不是11

SELECT REGEXP_SUBSTR('11a22A33a', '[^A]+', 4, 1, 'i') AS STR FROM DUAL;

结果：22
分析：正则表达式是以A为标识进行分割，而’i’标识不区分大小写，从第四个字符开始，取第一组截取结果，所以结果是22，而不是11

SELECT REGEXP_SUBSTR('11a22A33a', '[^A]+', 4, 1, 'c') AS STR FROM DUAL;

结果：22
分析：正则表达式是以A为标识进行分割，而’c’标识区分大小写，从第四个字符开始，取第一组截取结果，所以结果是22，而不是11a22

优化：

SELECT REGEXP_SUBSTR('11a22A33a', '[^A]+', 1, LEVEL, 'c') AS STR
  FROM DUAL
CONNECT BY LEVEL <= 5;

分析：此SQL的意思是把要输出来的第几组子串，通过一个变量 LEVEL 转换成输出前多少组子串。LEVEL <= 5代表的是输出前5组，没有足够多的子串则显示为NULL。这样显然会输出多余NULL值，不是我们想要的。

优化方案一：

--区分大小写
SELECT REGEXP_SUBSTR('11a22A33a', '[^A]+', 1, LEVEL, 'c') AS STR
  FROM DUAL
CONNECT BY LEVEL <= LENGTH('11a22A33a') - LENGTH(REGEXP_REPLACE('11a22A33a', 'A', '')) + 1;

--不区分大小写
SELECT REGEXP_SUBSTR('11a22A33a', '[^A]+', 1, LEVEL, 'i') AS STR
  FROM DUAL
CONNECT BY LEVEL <= LENGTH('11a22A33a') - LENGTH(REGEXP_REPLACE('11a22A33a', '[Aa]', '')) + 1;

分析：
以区分大小写SQL为例
LENGTH(‘11a22A33a’) 为总字符串的长度；
LENGTH(REGEXP_REPLACE(‘11a22A33a’, ‘A’, ‘’)) 为替换掉分隔符 ‘A’ 后剩余字符串的长度；
LENGTH(‘11a22A33a’) - LENGTH(REGEXP_REPLACE(‘11a22A33a’, ‘A’, ‘’)) 为替换掉分隔符 ‘A’ 的个数；
LENGTH(‘11a22A33a’) - LENGTH(REGEXP_REPLACE(‘11a22A33a’, ‘A’, ‘’)) + 1 为最终被分隔成的组数

优化方案二：

--区分大小写
SELECT REGEXP_SUBSTR('11a22A33a', '[^A]+', 1, LEVEL, 'c') AS STR
  FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('11a22A33a', '\A') + 1;

--不区分大小写
SELECT REGEXP_SUBSTR('11a22A33a', '[^A]+', 1, LEVEL, 'i') AS STR
  FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('11a22A33a', '[Aa]') + 1;

分析：
以区分大小写SQL为例
REGEXP_COUNT(‘11a22A33a’, ‘\A’) 为获取这个字符串中分隔符 ‘A’ 的个数
REGEXP_COUNT(‘11a22A33a’, ‘\A’) + 1 为最终被分隔成的组数

五、REGEXP_REPLACE()

REGEXP_REPLACE(STRING, REGEX, REPLACE_STRING)

STRING：需要进行正则处理的字符串
REGEX：进行匹配的正则表达式
REPLACE_STRING：要替换成的字符串

示例：

SELECT REGEXP_REPLACE('11a22A33a', 'a', '') AS STR FROM DUAL;

结果：1122A33

SELECT REGEXP_REPLACE('11a22A33a11a22A33a', '[^A]+', '#') AS STR FROM DUAL;

结果：#A#A#