SQL中的字符串正则表达式

正则表达式

正则表达式的基本概念

所谓正则表达式（Regular Expression，简称regex或regexp）是一种用于匹配字符串中字符组合的模式描述工具。它通过一系列特殊的字符和符号来定义一种约定俗成的规则，用于查找、匹配、替换或验证字符串中的特定内容。在表单提交验证、路由映射、文本处理等方面大放异彩，基本上所有编程语言都有特定的一套正则机制以供程序员使用。

正则表达式的基础字符

正则表达式由普通字符和特殊符号组成，一共可以分为两种大类。首先是元字符，由特殊符号组成，代表的是正则匹配中的匹配准则。转义字符通常由特殊符号\+字符组成，是某种字符表达类型的统称，当匹配内容中本身带有\时，需要再添加一个转义，例如\\。

元字符

• .：匹配任意一个字符（除了换行符）。
• *：匹配零次或多次的字符或子模式。
• +：匹配一次或多次的字符或子模式。
• ?：匹配零次或一次的字符或子模式。
• {n}：匹配重复n次的字符或子模式。
• {n,}：匹配重复≥n次的字符或子模式。
• {n,m}：匹配重复n到m次的字符或子模式。
• []：匹配方括号内的任意一个字符。
• [^]：匹配不在方括号内的任意一个字符。
• ^：表示匹配字符串以…开头。
• $：表示匹配字符串以…结尾。
• ()：分组，用于拆解匹配的内容。
• |：逻辑或，表示匹配多种模式中的任意一个。
• -：当其不在[]内则就代表字符-，在[]内表示范围，例如[a-z]表示任意小写字符。

转义字符

• \d：匹配任意数字字符（0-9）。
• \w：匹配任意字母或数字或下划线字符（等价于 [a-zA-Z0-9_]）。
• \s：匹配任意空白字符（空格、制表符、换行符等）。
• \b：匹配单词的开始与结束，例如\bword\b可以匹配字符串中的整个单词word，而不会混淆其他包含word的内容。
• \D：匹配任意非数字字符。
• \W：匹配任意非字母、数字、下划线字符（等价于 [^a-zA-Z0-9_]）。
• \S：匹配任意非空白字符。

常见的正则示例

1. 匹配电子邮件地址

\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b

解释：匹配以字母、数字、点号、下划线等开头（可出现一次或多次），后跟@，再跟转义的.，最后以域名和顶级域名（字母且至少有两个字符）结尾。

2. 匹配日期

\d{4}-\d{2}-\d{2}

解释：YYYY-MM-DD，匹配四个数字，跟-，后跟两个数字，再跟-，最后又是两个数字。

3. 匹配手机号码

^1[3-9]\d{9}$

解释：以1开头，第二位是3到9之间的数字，后面跟着九位数字结尾。

4. 匹配网页元素

<[^>]+>(.*)</[^>]+>

解释：<xxx>…</xxx>。

SQL中应用正则表达式

字符串查找

在SQL中若要应用正则表达式完成字符串查找功能，筛选出满足条件的记录时：

SELECT *
FROM 表名
WHERE 字符串字段 REGEXP '正则表达式';

注意：仅字符串字段支持使用REGEXP+正则语句进行查找功能。

字符串替换

在SQL中若要应用正则表达式完成字符串替换功能时：

SELECT regexp_replace(str, pattern, replace_str, occurrence)
FROM 表名;

其中：

• str：表示需要应用正则替换的字符串表达；
• pattern：表示正则表达式，需要带上英文引号；
• replace_str：表示当匹配到样式后需要将其替换的新字符串表达；
• occurrence：取值为整型常量，表示替换的程度，该值取0时替换掉所有满足正则条件的匹配子串，大于0时表示将第几个满足正则条件的匹配子串替换。

常见案例

例题： 查找有效邮箱的用户

一个有效的电子邮件具有前缀名称和域，其中：

• 前缀名称是一个字符串，可以包含字母（大写或小写），数字，下划线 _，点 . 和/或破折号- ；
• 前缀名称必须以字母开头；
• 域名称为@leetcode.com 。

代码：

SELECT user_id, name, mail
FROM Users
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';