进阶-正则匹配函数

1.引言

正则表达式是非常强大的文本搜索工具，不仅在SQL中有所应用，在各种编程语言中都发挥着重要作用

本文将对正则匹配作简单的介绍

2.正则表达式的组成部分

2.1.单字符


单个数字：0-9
单个大写字母：A-Z
单个小写字母：a-z（MySQL默认不区分大小写）
单个汉字：一-龥（UTF-8格式时）
单个符号：各种符号，包括%，@，♂，😀等
非打印字符：换行符，回车符，制表符，换页符等非打印字符
	\n：换行符
	\r：回车符
	\t：制表符
	\f：换页符
	\v：垂直制表符

2.2.字符集


.：除换行符和回车符外的任意单个字符
[0-9]：任意单个数字（不包括-）
[A-Z]：任意单个大写字母（不包括-）
[a-z]：任意单个小写字母（不包括-）
[一-龥]：任意单个汉字（不包括-）
[A男9]：A或男或9
\d：任意单个数字
\D：任意单个非数字
\s：任意单个非打印字符
\S：任意单个打印字符
\w：任意单个字母数字或下划线
\W：任意单个非字母数字或下划线的单个字符
[^A]：任意非A的单个字符
[^一-龥]：任意非汉字的单个字符（不包括-）
[^A男9]：任意非A或男或9的单个字符
A|一：A或一
[A-Z]|[0-9]：任意单个大写字母或数字（不包括-）
[^A-Z]|[^0-9]：任意非大写字母或数字（不包括-）的单个字符

2.3.边界字符


^：行首标志
$：行尾标志
\b：单词边界，字与空格
\B：非单词边界

2.4.模式分组


(pattern)：括号内的视为一个模组，其为捕获分组，即可引用
$n：引用分组，例如$1为引用首个分组
\n：引用分组，例如\1为引用首个分组

(?:pattern):非捕获分组，不可引用
(?<=pattern)：正后，放在需限定的模式前，此模式在下一模式之前出现
(?=pattern)：正前，放在需限定的模式后，此模式在下一模式之后出现
(?<!pattern)：反后，放在需限定的模式前，下一模式前不存在此模式
(?!pattern)：反前，放在需限定的模式后，下一模式后不存在此模式

(?'name'pattern)：命名捕获分组（不同语言的命名方式不一定相同，甚至不一定支持）
(?<name>pattern)：命名捕获分组（不同语言的命名方式不一定相同，甚至不一定支持）

2.5.量词


?：前一字符或模组出现0-1次
*：前一字符或模组出现0-∞次
+：前一字符或模组出现1-∞次
{}：前一字符或模组出现指定次数
	{2}：出现2次
	{0,3}：出现0到3次
	{5,}：出现5次及以上

2.6.转义字符


\：转义字符
[]：实际上除无法转义\和^外，[]本身也可充当转义字符

2.7.需转义的字符


-：在形如[A-C]，[0-一]，[一-龥]中-表示的是前一字符到后一字符的范围
^：在形如[^A-Z]中^表示的是非指定字符
|：或
.：任意单个字符
?：前一字符或模组出现0-1次
*：前一字符或模组出现0-∞次
+：前一字符或模组出现1-∞次
[：字符集[]的一部分
]：字符集[]的一部分
(：组()的一部分
)：组()的一部分
{：前一字符或模组出现指定次数{}的一部分
}：前一字符或模组出现指定次数{}的一部分
^：行首标志
$：行尾标志
\：转义字符

2.8.匹配模式


默认：贪婪模式，返回首次匹配的字符串，尽可能匹配更多字符，匹配失败会回溯
	?
	*
	+
	{}
?：懒惰模式，返回首次匹配的字符串，尽可能短
	??
	*?
	+?
	{}?
+：独占模式，尽可能匹配更多字符，匹配失败不会回溯（一些语言并不支持独占模式）
	?+
	*+
	++
	{}+

贪婪与懒惰：
存在字符串：DABBCABCDABCABCABC
贪婪匹配：A[A-C]+C，返回ABBCABC，不会返回ABCABCABC，因为其返回首次匹配成功的最长子串
懒惰匹配：A[A-C]+?C，返回ABBC，不会返回ABC，因为其返回首次匹配成功的最短子串

3.正则匹配函数

3.1.REGEXP_LIKE


SELECT 
	*,
	REGEXP_LIKE(column_0,'AB+C','c')
	# 查找是否存在匹配的子串，是则返回1，否则返回0
	# 查找形如：'ABC','ABBC','ABBBC'...的子串
	# 'c'表示区分大小写，'i'表示忽略大小写，默认忽略大小写
FROM 
	(VALUES 
	 	ROW('ABC'), # 匹配
	 	ROW('abc'),
	 	ROW('AB'),
	 	ROW('ABBC') # 匹配
	) AS T;

3.2.REGEXP_SUBSTR


# 贪婪模式
SELECT 
	*,
	REGEXP_SUBSTR(column_0,'A[A-C]+C',3,2,'c')
	# 从第三个字符起查找符合指定模式的子串，返回其中的第二个
	# 倒数第三项表示从第三个字符开始查找，倒数第二项表示返回第二个匹配子串
FROM 
	(VALUES 
	 	ROW('abbcabc'), # 不匹配
	 	ROW('DABBCBBBC'), # 从第三个字符起没有匹配的子串
        ROW('DDABCABBC'), # 只有一个匹配的子串（因为贪婪模式）
        ROW('ddabbcabcdabc'), # 大小写不匹配
        ROW('DDABBCABCDABBBC') # 匹配，ABBBC
	) AS T;
# 懒惰模式
SELECT 
	*,
	REGEXP_SUBSTR(column_0,'A[A-C]+?C',3,2,'c')
	# 从第三个字符起查找符合指定模式的子串，返回其中的第二个
	# 倒数第三项表示从第三个字符开始查找，倒数第二项表示返回第二个匹配子串
FROM 
	(VALUES 
	 	ROW('abbcabc'), # 不匹配
	 	ROW('DABBCBBBC'), # 从第三个字符起没有匹配的子串
        ROW('DDABCABBC'), # 匹配（因为懒惰模式），ABBC
        ROW('ddabbcabcdabc'), # 大小写不匹配
        ROW('DDABBCABCDABBBC') # 匹配，ABC
	) AS T;

3.3.REGEXP_REPLACE


# 贪婪模式
SELECT 
	*,
	REGEXP_REPLACE(column_0,'A[A-C]+C','X',3,2,'c')
	# 从第三个字符起查找符合指定模式的子串，将其中的第二个替换为X
	# 倒数第四项表示将匹配子串替换为X
FROM 
	(VALUES 
	 	ROW('abbcabc'), # 不匹配
	 	ROW('DABBCBBBC'), # 从第三个字符起没有匹配的子串
        ROW('DDABCABBC'), # 匹配（因为懒惰模式）
        ROW('ddabbcabcdabc'), # 大小写不匹配
        ROW('DDABBCABCDABBBC') # 匹配，ABBBC
	) AS T;
# 懒惰模式    
SELECT 
	*,
	REGEXP_REPLACE(column_0,'A[A-C]+?C','X',3,2,'c')
	# 从第三个字符起查找符合指定模式的子串，将其中的第二个替换为X
	# 倒数第四项表示将匹配子串替换为X
FROM 
	(VALUES 
	 	ROW('abbcabc'), # 不匹配
	 	ROW('DABBCBBBC'),# 从第三个字符起没有匹配的子串
        ROW('DDABCABBC'), # 匹配（因为懒惰模式），ABBC替换为X
        ROW('ddabbcabcdabc'), # 大小写不匹配
        ROW('DDABBCABCDABBBC') # 匹配，ABC替换为X
	) AS T;

3.4.REGEXP_INSTR


# 贪婪模式
SELECT 
	*,
	REGEXP_INSTR(column_0,'A[A-C]+C',3,2,1,'c')
	# 从第三个字符起查找符合指定模式的子串，返回其中的第二个的结束下标+1（下标从1开始）
	# 倒数第二项为1表示返回匹配子串的结束下标的下一位
FROM 
	(VALUES 
	 	ROW('abbcabc'), # 不匹配
	 	ROW('DABBCBBBC'), # 从第三个字符起没有匹配的子串
        ROW('DDABCABBCDDD'), # 匹配（因为懒惰模式）
        ROW('ddabbcabcdabc'), # 大小写不匹配
        ROW('DDABBCABCDABBBCDDD') # 匹配，ABBBC结束下标的下一位为16
	) AS T;
# 懒惰模式    
SELECT 
	*,
	REGEXP_INSTR(column_0,'A[A-C]+?C',3,2,0,'c')
	# 从第三个字符起查找符合指定模式的子串，返回其中的第二个的起始下标（下标从1开始）
	# 倒数第二项为0表示返回匹配子串的起始下标
FROM 
	(VALUES 
	 	ROW('abbcabc'), # 不匹配
	 	ROW('DABBCBBBC'),# 从第三个字符起没有匹配的子串
        ROW('DDABCABBC'), # 匹配（因为懒惰模式），ABBC起始下标为6
        ROW('ddabbcabcdabc'), # 大小写不匹配
        ROW('DDABBCABCDABBBC') # 匹配，ABC起始下标为7
	) AS T;