文章目录
准备工作
LIKE 运算符
转义字符
数据库扩展
MySQL 正则表达式
Oracle 正则表达式
SQL Server 正则表达式
PostgreSQL 正则表达式
SQLite 正则表达式
参考文档
字符串的模式匹配(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 个字母。
对于以上规则,使用正则表达式可以描述如下:
1+[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,4}$
简单说明一下,^ 表示匹配字符串的开头;[a-zA-Z0-9] 匹配大小写字母或数字;+ 表示匹配前面的内容一次或多次;. 匹配任何一个字符,. 匹配点号自身;{2,4} 匹配前面的内容 2 次到 4次;$ 表示字符串的结束。
📝关于正则表达式的详细介绍,可以参考 GitHub 上的正则表达式教程。
LIKE 运算符
SQL 标准定义了 LIKE运算符,可以用于简单的模式匹配。其中有两个特殊的通配符:
下划线(_)匹配一个任意字符;
百分号(%)匹配零个或者多个任意字符。
以下语句用于查找以 test 开头的字符串:
select email
from test
where email like ‘test%’;
– MySQL、SQL Server 和 SQLite
TEST@qq.com |
test@qq |
test+email@sina.cn |
– Oracle 和 PostgreSQL
test@qq |
test+email@sina.cn |
从查询结果可以看出,MySQL、SQL Server 和 SQLite 中的LIKE运算符默认不区分大小写;Oracle 和 PostgreSQL 中的LIKE运算符默认区分大小写。
以下示例返回了由 4 个字符加上 @ 符号开始的字符串:
select email
from test
where email like ‘____@%’;
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 中的范围匹配,例如:
– SQL Server
select email
from test
where email like ‘[0-9]%’;
123.test@sql.org |
其中,[0-9] 表示任意数字;所以上面的查询返回了以数字开头的字符串。同理,’[^0-9]%'匹配不以数字开头的字符串,%[ace]匹配以 a、c 或者 e 结束的字符串。
LIKE运算符适合简单的模式匹配。当我们需要匹配复杂的模式时,例如合法的电子邮箱,无法使用LIKE运算符实现。此时,需要使用更强大的正则表达式。