字符串的模式匹配（pattern matching）是指给定一个模式，然后判断某个字符串是否满足该模式。常见的应用包括敏感词的检测、身份证校验、IP 地址验证等。

本文以验证电子邮箱的合法性为例，介绍如何在 SQL 中实现字符串的模式匹配，涉及的数据库包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。下表列出了这些数据库对于模式匹配的支持情况：

接下来我们详细讨论不同数据库的具体实现细节。

准备工作

我们首先创建一个测试表，并且生成一些测试数据：

CREATE TABLE test (
  email VARCHAR(50)
);

INSERT INTO test VALUES ('TEST@qq.com');
INSERT INTO test VALUES ('test@qq');
INSERT INTO test VALUES ('.123@qq.com');
INSERT INTO test VALUES ('test+email@sina.cn');
INSERT INTO test VALUES ('me.qq.com');
INSERT INTO test VALUES ('123.test@sql.org');

对于 Web 开发中常见的邮箱地址验证问题，我们首先需要定义一个合法电子邮箱的规则。电子邮箱一般格式为：用户名@域名；以下是一个简单的规则：

• 以字母或者数字开头，后面跟一个或者多个字母、数组或特殊字符（ . _ - ）；

• 然后是一个 @ 字符；

• 接着是一个或者多个字母、数组或特殊字符（ . - ）；

• 最后是域名，即 . 以及 2 到 4 个字母。

^[a-zA-Z0-9]+[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$

简单说明一下，^ 表示匹配字符串的开头；[a-zA-Z0-9] 匹配大小写字母或数字；+ 表示匹配前面的内容一次或多次；. 匹配任何一个字符，\. 匹配点号自身；{2,4} 匹配前面的内容 2 次到 4次；$ 表示字符串的结束。

📝关于正则表达式的详细介绍，可以参考 GitHub 上的正则表达式教程：https://github.com/ziishaned/learn-regex/blob/master/translations/README-cn.md。

LIKE 运算符

SQL 标准定义了 LIKE运算符，可以用于简单的模式匹配。其中有两个特殊的通配符：

• 下划线（_）匹配一个任意字符；

• 百分号（%）匹配零个或者多个任意字符。

select email
from test
where email like 'test%';

-- MySQL、SQL Server 和 SQLite
email             |
------------------|
TEST@qq.com       |
test@qq           |
test+email@sina.cn|

-- Oracle 和 PostgreSQL
EMAIL             |
------------------|
test@qq           |
test+email@sina.cn|

从查询结果可以看出，MySQL、SQL Server 和 SQLite 中的LIKE运算符默认不区分大小写；Oracle 和 PostgreSQL 中的LIKE运算符默认区分大小写。

以下示例返回了由 4 个字符加上 @ 符号开始的字符串：

select email
from test
where email like '____@%';
email      |
-----------|
TEST@qq.com|
test@qq    |
.123@qq.com|

另外，NOT LIKE运算符可以进行反向匹配，也就是查找不匹配某个模式的字符串。

转义字符

由于下划线和百分号被看作通配符，我们无法判断某个字符串中是否包含这两个字符。此时需要用到转义字符，它的作用就是将随后的字符看作普通字符，例如：

'60%' like '60!%' escape '!' -- 匹配
'60%' like '60\%' -- 匹配
'60@' like '60!%' escape '!' -- 不匹配

其中，escape 用于指定转义字符，默认情况下为反斜杠（\）。

数据库扩展

除了 SQL 标准中定义的行为之外，许多数据库对LIKE运算符进行了扩展：

• MySQL 和 SQLite 允许对数字类型进行模式匹配：10 LIKE '1%'；

• Oracle 提供了LIKEC、LIKE2、LIKE4用于不同字符集的字符匹配；

• SQL Server 支持使用 [ ] 匹配某个范围内的字符，[^ ] 匹配某个范围之外的字符；

• PostgreSQL 提供了不区分大小写的ILIKE运算符；

• SQLite 提供了区分大小写的编译选项PRAGMA case_sensitive_like = true;，同时还提供了基于 Unix 文件路径匹配的 GLOB 运算符。

-- SQL Server
select email
from test
where email like '[0-9]%';
email           |
----------------|
123.test@sql.org|

其中，[0-9] 表示任意数字；所以上面的查询返回了以数字开头的字符串。同理，'[^0-9]%'匹配不以数字开头的字符串，%[ace]匹配以 a、c 或者 e 结束的字符串。

LIKE运算符适合简单的模式匹配。当我们需要匹配复杂的模式时，例如合法的电子邮箱，无法使用LIKE运算符实现。此时，需要使用更强大的正则表达式。

MySQL 正则表达式

MySQL 提供了 REGEXP_LIKE 函数，用于实现正则表达式匹配：

REGEXP_LIKE(expr, pat[, match_type])

如果表达式 expr 匹配模式 pat，该函数返回 1，否则返回 0；如果 expr 或者 pat 为 NULL，该函数返回 NULL。可选的参数 match_type 可以指定多个额外的匹配选项：

• c：区分大小写；

• i：不区分大小写；

• m：多行模式，^ 和 $ 能够匹配字符串中的行终止符。默认情况下它们只匹配 expr 的开头和结尾；

• n：字符 . 能够匹配行终止符。默认情况下 . 遇到行尾将会匹配；

• u：只匹配 Unix 行终止符，只有换行符（\n）能够匹配 .、^ 以及 $。

select email
from test
where regexp_like(email, '^[a-z0-9]+[a-z0-9._-]+@[a-z0-9.-]+\\.[a-z]{2,4}$');
email           |
----------------|
TEST@qq.com     |
123.test@sql.org|

查询结果显示只有 2 个电子邮箱是合法的。注意，模式中的反斜杠需要写双份，因为 MySQL 使用 C 语言风格的转义语法。

另外，REGEXP 和 RLIKE 运算符是 REGEXP_LIKE 函数的同义词：

expr REGEXP pat
expr RLIKE pat

Oracle 正则表达式

Oracle 提供了与 MySQL 类似的 REGEXP_LIKE 函数，用于执行正则表达式匹配：

REGEXP_LIKE(source_char, pattern[, match_param ])

Oracle 中的 REGEXP_LIKE 只能作为条件使用；如果字符串 source_char 匹配模式 pat 就返回结果；否则不返回结果。可选的参数 match_param 可以指定多个额外的匹配选项：

• c 表示区分大小写和重音；

• i 表示不区分大小写和重音；

• n 表示符号 . 能够匹配换行符（\n）。默认情况下不匹配换行符；

• m 表示多行模式，^ 和 $ 能够匹配字符串中的任何行的开始和结尾，而不仅仅是整个字符串的开头和结尾；

• x 表示忽略模式中的空白字符，默认情况下空白字符匹配它们自身。

对于上文中的电子邮箱校验，Oracle 中的实现如下：

select email
from test
where regexp_like(email, '^[a-z0-9]+[a-z0-9._-]+@[a-z0-9.-]+\.[a-z]{2,4}$', 'i');
email           |
----------------|
TEST@qq.com     |
123.test@sql.org|

选项 i 表示不区分大小写。

SQL Server 正则表达式

SQL Server 目前没有提供类似其他数据库的正则表达式函数或者运算符，可以通过 CLR 编写自定义函数实现。

PostgreSQL 正则表达式

PostgreSQL 提供了一组相关的正则表达式匹配运算符：

• expr ~ pattern，正则表达式匹配，区分大小写；

• expr ~* pattern，正则表达式匹配，不区分大小写；

• expr !~ pattern，不匹配正则表达式，区分大小写；

• expr !~* pattern，不匹配正则表达式，区分大小写。

select email
from test
where email ~* '^[a-z0-9]+[a-z0-9._-]+@[a-z0-9.-]+\.[a-z]{2,4}$';
email           |
----------------|
TEST@qq.com     |
123.test@sql.org|

上例中使用了不区分大小写的匹配运算符。

另外，PostgreSQL 还提供了一个运算符SIMILAR TO，用于执行一种特殊的 SQL 正则表达式匹配。不过，SQL 标准已经删除了这个运算符，不推荐使用。

SQLite 正则表达式

SQLite 默认没有提供了正则表达式匹配的运算符，但是预定义了 X REGEXP Y 运算符接口，它实际上是调用了 regexp(Y,X) 用户函数。因此，我们可以通过创建一个函数 regexp(Y,X)，例如安装并加载 sqlite3-pcre 模块，就可以获得基于 Perl 的正则表达式。