正则函数 [ REGEXP_INSTR()、REGEXP_LIKE()、REGEXP_REPLACE()、REGEXP_SUBSTR() ]

Exisi 2023-02-15 14:30:13

Categories： Tags：

正则函数	说明
REGEXP_INSTR()	子字符串匹配正则表达式的起始索引
REGEXP_LIKE()	字符串是否与正则表达式匹配
REGEXP_REPLACE()	替换与正则表达式匹配的子字符串
REGEXP_SUBSTR()	返回与正则表达式匹配的子字符串

REGEXP_INSTR()

返回与模式pat指定的正则表达式匹配的字符串 expr 的子字符串的起始索引，如果不匹配，则返回0。如果expr或pat为NULL，则返回值为NULL。字符索引从1开始。

语法

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

参数

说明

exp

匹配的字符

pat

字符匹配模式，以下模式

参数类型	作用	适用
str	匹配包含指定的字符串的文本	aa -> aa
[ ]	匹配包含指定的字符串的文本	[ab] -> a, b, ab, bc, ac
[^ ]	匹配不在括号中的任何字符	[ab] -> c, cde
^	匹配字符串的开始位置	^a -> abc
$	匹配字符串的结束位置	C$ -> ABC
.	匹配任何一个字符，包括回车、换行等。	b.t -> bit, bat, but
*	匹配前面的一个字符出现 0 个或 n 次，在它之前必须有内容	f*n -> fan, fn, an
+	匹配前面的一个字符出现 1 个或 n 次，在它之前也必须有内容	ba+ -> ba, bare, bay
?	匹配前面的一个字符出现 0 个或 1 次	sa? -> sa, s
{n}	匹配前面的一个字符出现 n 次	b{2} -> bb
{n,}	配前面的一个字符出现 n 次或着更多次	b{2,} -> bb, bbb，bbbb
{n,m}	配前面的一个字符出现最 n ～ m次	b{3,7} -> bbb，bbbbbbb

pos

表达式中开始搜索的位置。如果省略，默认值为1。

occurrence

要搜索匹配项的出现次数。如果省略，默认值为1

return_option

返回哪种类型的位置。如果此值为 0，则 REGEXP_INSTR() 返回匹配的子字符串的第一个字符的位置。如果此值为 1，则 REGEXP_INSTR() 返回匹配子字符串之后的位置。如果省略，则默认值为 0。

match_type

指定如何执行匹配的字符串。

参数	描述
c	区分大小写的匹配。
i	不区分大小写的匹配。
m	多行模式。识别字符串中的行终止符。默认行为是仅在字符串表达式的开头和结尾匹配行终止符。
n	字符匹配行终止符。默认为 . 匹配到在一行的末尾停止。
u	仅 Unix 的行尾。只有换行符被 .,^ 和 $ 匹配运算符识别为以行结尾的行。

示例

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog');

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

| REGEXP_INSTR('dog cat dog', 'dog') |

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

| 1 |

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

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);

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

| REGEXP_INSTR('dog cat dog', 'dog', 2) |

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

| 9 |

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

mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');

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

| REGEXP_INSTR('aa aaa aaaa', 'a{2}') |

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

| 1 |

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

REGEXP_LIKE()

如果字符串 expr 与模式 pat 指定的正则表达式匹配，则返回 1，否则返回 0。如果 expr 或 pat 为 NULL，则返回值为 NULL。

语法

REGEXP_LIKE(expr, pat[, match_type])

参数

说明

exp

匹配的字符

pat

字符匹配模式，以下模式

模式	作用	适用
str	匹配包含指定的字符串的文本	aa -> aa
[ ]	匹配包含指定的字符串的文本	[ab] -> a, b, ab, bc, ac
[^ ]	匹配不在括号中的任何字符	[ab] -> c, cde
^	匹配字符串的开始位置	^a -> abc
$	匹配字符串的结束位置	C$ -> ABC
.	匹配任何一个字符，包括回车、换行等。	b.t -> bit, bat, but
*	匹配前面的一个字符出现 0 个或 n 次，在它之前必须有内容	f*n -> fan, fn, an
+	匹配前面的一个字符出现 1 个或 n 次，在它之前也必须有内容	ba+ -> ba, bare, bay
?	匹配前面的一个字符出现 0 个或 1 次	sa? -> sa, s
{n}	匹配前面的一个字符出现 n 次	b{2} -> bb
{n,}	配前面的一个字符出现 n 次或着更多次	b{2,} -> bb, bbb，bbbb
{n,m}	配前面的一个字符出现最 n ～ m次	b{3,7} -> bbb，bbbbbbb

match_type

指定如何执行匹配的字符串。

参数	描述
c	区分大小写的匹配。
i	不区分大小写的匹配。
m	多行模式。识别字符串中的行终止符。默认行为是仅在字符串表达式的开头和结尾匹配行终止符。
n	字符匹配行终止符。默认为 . 匹配到在一行的末尾停止。
u	仅 Unix 的行尾。只有换行符被 .,^ 和 $ 匹配运算符识别为以行结尾的行。

示例

mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');

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

| REGEXP_LIKE('CamelCase', 'CAMELCASE') |

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

| 1 |

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

mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs);

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

| REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs) |

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

| 0 |

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

注

如果在 match_type 中指定了指定矛盾选项的字符，则最右边的优先。

默认情况下，正则表达式操作在确定字符类型和执行比较时使用 expr 和 pat 参数的字符集和排序规则。如果参数具有不同的字符集或排序规则，则适用强制规则。可以使用显式排序指示符指定参数以更改比较行为。

match_type 可以用 c 或 i 字符指定以覆盖默认的区分大小写。例外：如果任一参数是二进制字符串，则参数以区分大小写的方式作为二进制字符串处理，即使 match_type 包含 i 字符。

REGEXP_REPLACE()

用替换字符串 repl 替换字符串 expr 中匹配由模式 pat 指定的正则表达式的匹配项，并返回结果字符串。如果 expr、pat 或 repl 为 NULL，则返回值为 NULL。

语法

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

参数

说明

exp

匹配的字符

pat

字符匹配模式，以下模式

模式	作用	适用
str	匹配包含指定的字符串的文本	aa -> aa
[ ]	匹配包含指定的字符串的文本	[ab] -> a, b, ab, bc, ac
[^ ]	匹配不在括号中的任何字符	[ab] -> c, cde
^	匹配字符串的开始位置	^a -> abc
$	匹配字符串的结束位置	C$ -> ABC
.	匹配任何一个字符，包括回车、换行等。	b.t -> bit, bat, but
*	匹配前面的一个字符出现 0 个或 n 次，在它之前必须有内容	f*n -> fan, fn, an
+	匹配前面的一个字符出现 1 个或 n 次，在它之前也必须有内容	ba+ -> ba, bare, bay
?	匹配前面的一个字符出现 0 个或 1 次	sa? -> sa, s
{n}	匹配前面的一个字符出现 n 次	b{2} -> bb
{n,}	配前面的一个字符出现 n 次或着更多次	b{2,} -> bb, bbb，bbbb
{n,m}	配前面的一个字符出现最 n ～ m次	b{3,7} -> bbb，bbbbbbb

pos

表达式中开始搜索的位置。如果省略，默认值为1。

occurrence

要替换匹配项的哪个匹配项。如果省略，默认值为0（表示“替换所有引用”）。

match_type

指定如何执行匹配的字符串。

参数	描述
c	区分大小写的匹配。
i	不区分大小写的匹配。
m	多行模式。识别字符串中的行终止符。默认行为是仅在字符串表达式的开头和结尾匹配行终止符。
n	字符匹配行终止符。默认为 . 匹配到在一行的末尾停止。
u	仅 Unix 的行尾。只有换行符被 .,^ 和 $ 匹配运算符识别为以行结尾的行。

示例

mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');

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

| REGEXP_REPLACE('a b c', 'b', 'X') |

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

| a X c |

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

mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);

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

| REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) |

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

| abc def X |

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

注

在 MySQL 8.0.17 之前，该函数返回的结果使用 UTF-16 字符集；在 MySQL 8.0.17 及更高版本中，使用搜索匹配的表达式的字符集和排序规则。（错误 #94203、错误 #29308212）

REGEXP_SUBSTR()

返回与模式 pat 指定的正则表达式匹配的字符串 expr 的子字符串，如果没有匹配则返回 NULL。如果 expr 或 pat 为 NULL，则返回值为 NULL

语法

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

参数

说明

exp

匹配的字符

pat

字符匹配模式，以下模式

模式	作用	适用
str	匹配包含指定的字符串的文本	aa -> aa
[ ]	匹配包含指定的字符串的文本	[ab] -> a, b, ab, bc, ac
[^ ]	匹配不在括号中的任何字符	[ab] -> c, cde
^	匹配字符串的开始位置	^a -> abc
$	匹配字符串的结束位置	C$ -> ABC
.	匹配任何一个字符，包括回车、换行等。	b.t -> bit, bat, but
*	匹配前面的一个字符出现 0 个或 n 次，在它之前必须有内容	f*n -> fan, fn, an
+	匹配前面的一个字符出现 1 个或 n 次，在它之前也必须有内容	ba+ -> ba, bare, bay
?	匹配前面的一个字符出现 0 个或 1 次	sa? -> sa, s
{n}	匹配前面的一个字符出现 n 次	b{2} -> bb
{n,}	配前面的一个字符出现 n 次或着更多次	b{2,} -> bb, bbb，bbbb
{n,m}	配前面的一个字符出现最 n ～ m次	b{3,7} -> bbb，bbbbbbb

pos

表达式中开始搜索的位置。如果省略，默认值为1。

occurrence

要搜索的匹配项。如果省略，默认为 1

match_type

指定如何执行匹配的字符串。

参数	描述
c	区分大小写的匹配。
i	不区分大小写的匹配。
m	多行模式。识别字符串中的行终止符。默认行为是仅在字符串表达式的开头和结尾匹配行终止符。
n	字符匹配行终止符。默认为 . 匹配到在一行的末尾停止。
u	仅 Unix 的行尾。只有换行符被 .,^ 和 $ 匹配运算符识别为以行结尾的行。

示例

mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');

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

| REGEXP_SUBSTR('abc def ghi', '[a-z]+') |

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

| abc |

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

mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);

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

| REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |

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

| ghi |

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

注

在 MySQL 8.0.17 之前，该函数返回的结果使用 UTF-16 字符集；在 MySQL 8.0.17 及更高版本中，使用搜索匹配的表达式的字符集和排序规则。（错误 #94203、错误 #29308212）

来自 <https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-instr>

正则关键字（REGEXP／RLIKE／NOT REGEXP／NOT RLIKE）

分组（GROUP BY）