[1173]regexp_replace()、regexp_substr()、regexp_instr()函数的用法

周小董

已于 2022-12-05 16:16:07 修改

阅读量1.1w

点赞数 13

分类专栏：数据库&中间件文章标签：正则表达式

于 2022-12-03 22:33:33 首次发布

本文链接：https://blog.csdn.net/xc_zhou/article/details/128167611

版权

数据库&中间件专栏收录该内容

123 篇文章 13 订阅

订阅专栏

文章目录

REGEXP_REPLACE的使用方法

命令格式：regexp_replace(source, pattern, replace_string, occurrence)

参数说明

source: string类型，要替换的原始字符串。
pattern: string类型常量，要匹配的正则模式，pattern为空串时抛异常。
replace_string:string，将匹配的pattern替换成的字符串。
occurrence: bigint类型常量，必须大于等于0。
- 大于0：表示将第几次匹配替换成replace_string。
- 等于0：表示替换掉所有的匹配子串。
- 其它类型或小于0抛异常。

返回值

将source字符串中匹配pattern的子串替换成指定字符串后返回，当输入source, pattern, occurrence参数为NULL时返回NULL，若replace_string为NULL且pattern有匹配，返回NULL，replace_string为NULL但pattern不匹配，则返回原串。

常用案例

1、用#替换字符串中的所有数字

SELECT regexp_replace('01234abcde56789','[0-9]','#');

结果：#####abcde#####

用#替换字符串中的数字0、9

SELECT regexp_replace('01234abcde56789','[09]','#');

结果：#1234abcde5678#

2、遇到非小写字母或者数字跳过，从匹配到的第4个值开始替换，替换为''

SELECT regexp_replace('abcdefg123456ABC','[a-z0-9]','',4)

结果：abcefg123456ABC

SELECT regexp_replace('abcDEfg123456ABC','[a-z0-9]','',4)

结果：abcDEg123456ABC

SELECT regexp_replace('abcDEfg123456ABC','[a-z0-9]','',7);

结果：abcDEfg13456ABC

遇到非小写字母或者非数字跳过，将所有匹配到的值替换为''

SELECT regexp_replace('abcDefg123456ABC','[a-z0-9]','',0);

结果：DABC

3、格式化手机号，将+86 13811112222转换为(+86) 138-1111-2222,+在正则表达式中有定义，需要转义。\\1表示引用的第一个组

SELECT regexp_replace('+86 13811112222','(\\+[0-9]{2})( )([0-9]{3})([0-9]{4})([0-9]{4})','(\\1)\\3-\\4-\\5',0);

结果：(+86)138-1111-2222

SELECT regexp_replace("123.456.7890","([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})","(\\1)\\2-\\3",0) ;
SELECT regexp_replace("123.456.7890","([0-9]{3})\\.([0-9]{3})\\.([0-9]{4})","(\\1)\\2-\\3",0) ;

结果：(123)456-7890

4、将字符用空格分隔开，0表示替换掉所有的匹配子串。

SELECT regexp_replace('abcdefg123456ABC','(.)','\\1 ',0) AS new_str FROM dual;

结果：a b c d e f g 1 2 3 4 5 6 A B C

SELECT regexp_replace('abcdefg123456ABC','(.)','\\1 ',2) AS new_str FROM dual;

结果：ab cdefg123456ABC

5、

SELECT regexp_replace("abcd","(.*)(.)$","\\1",0) ;

结果：abc

SELECT regexp_replace("abcd","(.*)(.)$","\\2",0) ;

结果：d

SELECT regexp_replace("abcd","(.*)(.)$","\\1-\\2",0) ;

结果：abc-d

其他案例

SELECT regexp_replace("abcd","(.)","\\2",1) 结果为`"abcd"`，因为pattern中只定义了一个组，引用的第二个组不存在。
SELECT regexp_replace("abcd","(.*)(.)$","\\2",0) 结果为"d"
SELECT regexp_replace("abcd","(.*)(.)$","\\1",0) 结果为"abc"
SELECT regexp_replace("abcd","(.*)(.)$","\\1-\\2",0) 结果为"abc-d"
SELECT regexp_replace("abcd","a","\\1",0)，结果为” \1bcd”，因为在pattern中没有组的定义，所以\1直接输出为字符。

正则符号释义

regexp_substr()函数的用法

在SQL中尝试使用正则，可以试下regexp_substr()来进行分割

1.首先创建一个实验视图：

SQL>

create or replace view test_ip as select '192.168.1.1' as ip from dual
 union all
select '192.168.1.2' as  ip from dual 
union all 
select '192.168.1.3' as  ip from dual
union all
select '192.168.1.4' as  ip from dual;

2.查看下视图的整体结构

SQL> select * from test_ip;

IP
-----------
192.168.1.1
192.168.1.2
192.168.1.3
192.168.1.4

3.实例
（1）现在有一个需求，需要将这些ip以“.”为分隔符，分段显示ip
最终效果如下：

IP1                    IP2                    IP3                    IP4
---------------------- ---------------------- ---------------------- -----
192                    168                    1                      1
192                    168                    1                      2
192                    168                    1                      3
192                    168                    1                      4

执行的SQL如下：

select regexp_substr(a.ip, '[^.]+', 1, 1) ip1,
regexp_substr(a.ip, '[^.]+', 1, 2) ip2,
regexp_substr(a.ip, '[^.]+', 1, 3) ip3,
regexp_substr(a.ip, '[^.]+', 1, 4) ip4 from test_ip a;

分析：

regexp_substr()括号中的
[^.]   -->代表除了“.”以外的全部字段
+      -->表示匹配1次以上
1      -->表示从第一个“.”开始
2      -->表示匹配到的第二个字段

这样就能达到这个效果

regexp_instr()函数的用法

在 MySQL 中， REGEXP_INSTR() 函数返回与正则表达式模式匹配的子字符串的起始索引。

索引从 1 开始。如果不匹配，则返回 0。

语法如下

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])

expr 为源字符串，pat 为正则表达式。
pos 为可选参数，标识开始匹配的位置，默认为1。
occurrence 为可选参数，标识匹配的次数，默认为 1。
return_option 为可选参数，指定返回值的类型。如果为 0，则返回匹配的第一个字符的位置。如果为 1，则返回匹配的最后一个位置，默认为 0。
match_type 为可选参数，允许优化正则表达式。例如，可以使用此参数指定是否区分大小写。

示例 1 – 基本用法

基本示例：

SELECT REGEXP_INSTR('Cat', 'at') Result;

结果：

+--------+

| Result |

+--------+

| 2 |

+--------+

子字符串从位置 2 开始存在匹配项。

示例 2 – 不匹配

不匹配的示例：

SELECT REGEXP_INSTR('Cat', '^at') Result;

结果：

+--------+

| Result |

+--------+

| 0 |

+--------+

没有匹配项，因为指定字符串必须从子字符串开始，结果返回 0。

让我们把它改为该子字符串开头：

SELECT REGEXP_INSTR('at', '^at') Result;

结果：

+--------+

| Result |

+--------+

| 1 |

+--------+

示例 3 – 参数 pos

指定开始位置：

SELECT REGEXP_INSTR('Cat Cat', 'Cat', 2) Result;

结果：

+--------+

| Result |

+--------+

| 5 |

+--------+

我们得到了第二次出现的索引。

请注意，无论在哪里指定起始位置，索引都将从位置 1 开始计数。

以下示例更加清楚地说明了这一点：

SELECT

REGEXP_INSTR('Cat Cat', 'Cat', 2) AS 'Pos 2',

REGEXP_INSTR('Cat Cat', 'Cat', 3) AS 'Pos 3',

REGEXP_INSTR('Cat Cat', 'Cat', 5) AS 'Pos 5';

结果：

+-------+-------+-------+

| Pos 2 | Pos 3 | Pos 5 |

+-------+-------+-------+

| 5 | 5 | 5 |

+-------+-------+-------+

当然，根据您的正则表达式模式，可以返回完全不同的子字符串的索引。例：

SELECT

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1) 'Pos 1',

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2) 'Pos 2',

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 6) 'Pos 6';

结果：

+-------+-------+-------+

| Pos 1 | Pos 2 | Pos 6 |

+-------+-------+-------+

| 1 | 5 | 16 |

+-------+-------+-------+

我们可以使用 REGEXP_SUBSTR() 函数检查子字符串：

SELECT

REGEXP_SUBSTR('Cat City is SO Cute!', 'C.t', 1) 'Pos 1',

REGEXP_SUBSTR('Cat City is SO Cute!', 'C.t', 2) 'Pos 2',

REGEXP_SUBSTR('Cat City is SO Cute!', 'C.t', 6) 'Pos 6';

结果：

+-------+-------+-------+

| Pos 1 | Pos 2 | Pos 6 |

+-------+-------+-------+

| Cat | Cit | Cut |

+-------+-------+-------+

示例 4 – 参数 occurrence

这是使用参数 occurrence 的示例。在所有情况下，我们都从位置 1开始：

SELECT

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1) 'Occurrence 1',

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2) 'Occurrence 2',

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3) 'Occurrence 3';

结果：

+--------------+--------------+--------------+

| Occurrence 1 | Occurrence 2 | Occurrence 3 |

+--------------+--------------+--------------+

| 1 | 5 | 16 |

+--------------+--------------+--------------+

但是，如果我们从不同位置开始，结果将会有所不同：

SELECT

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 1) 'Occurrence 1',

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 2) 'Occurrence 2',

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 3) 'Occurrence 3';

结果：

+--------------+--------------+--------------+

| Occurrence 1 | Occurrence 2 | Occurrence 3 |

+--------------+--------------+--------------+

| 5 | 16 | 0 |

+--------------+--------------+--------------+

发生这种情况是因为我们的起始位置是在第一次出现之后开始的。因此，2 次匹配变成 1 次匹配的结果，3 次匹配变成 2 次匹配的结果。并且由于不存在更多的次数，因此 3 次匹配的结果为零(即没有达到 3 次匹配)。

示例 5 – 参数 return_option

以下是使用参数 return_option 的示例：

SELECT

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 0) 'Option 0',

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 1) 'Option 1';

结果：

+----------+----------+

| Option 0 | Option 1 |

+----------+----------+

| 1 | 4 |

+----------+----------+

Option 0 返回了匹配的第一个字符的位置。Option 1 返回了匹配之后的位置。

如果将其应用于上一个示例，如下：

SELECT

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 0) 'Occurrence 1',

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2, 0) 'Occurrence 2',

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3, 0) 'Occurrence 3'

UNION ALL

SELECT

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 1),

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2, 1),

REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3, 1);

结果：

+--------------+--------------+--------------+

| Occurrence 1 | Occurrence 2 | Occurrence 3 |

+--------------+--------------+--------------+

| 1 | 5 | 16 |

| 4 | 8 | 19 |

+--------------+--------------+--------------+

在这种情况下，我们使用 Option 0 设置了一组，使用 Option 1 设置了一组，然后使用 UNION ALL 将它们连接在一起。

示例 6 – 参数 match_type

您可以使用其他参数来确定匹配类型。可以指定诸如匹配是否区分大小写，是否包括行终止符之类的内容。

这是指定区分大小写的匹配和不区分大小写的匹配的示例：

SELECT

REGEXP_INSTR('Cat City is SO Cute!', 'c.t', 1, 1, 0, 'c') 'Case-Sensitive',

REGEXP_INSTR('Cat City is SO Cute!', 'c.t', 1, 1, 0, 'i') 'Case-Insensitive';

结果：

+----------------+------------------+

| Case-Sensitive | Case-Insensitive |

+----------------+------------------+

| 0 | 1 |

+----------------+------------------+

参数 match_type 可以包含以下字符：

c：匹配区分大小写。
i：匹配不区分大小写。
m：多行模式，识别字符串中的行终止符，默认是仅在字符串表达式的开头和结尾匹配行终止符。
n：与 . 行终止符匹配。
u：仅匹配 Unix 的行结尾。只有换行符被识别为以 .， ^ 和 $ 结尾的行。

参考：https://blog.csdn.net/JohnnyChu/article/details/111184962
https://blog.csdn.net/boos_zhao/article/details/121470300
https://blog.csdn.net/weixin_30438795/article/details/113254243