结构化查询语言(Structured Query Language)
自 SQL 诞生之日起,就包含了一种基本形式的字符串模式匹配功能。此功能作为 WHERE 子句的一部分,通过关键字 LIKE 触发。它包含两个通配符:_
(下划线)和 %
(百分号)。
SELECT *
FROM <table_name>
WHERE <column_name> LIKE <like_criterion>; -- like_criterion 可包含 '_' 和 '%'
随着时间的推移,SQL 标准逐渐引入了其他功能,包括类似 POSIX 定义的正则表达式、XQuery 1.0 / XPath 2.0 函数 fn:matches()
的正则表达式,以及 ISO/IEC 13249-2:2003 第 2 部分中针对全文检索的定义。
LIKE 模式匹配
LIKE 谓词将类型为 CHAR 或 VARCHAR(字符串)的列与一个模式进行比较。模式也是一个字符串,可以包含两种具有特殊含义的字符:
_
(下划线):表示任意 一个 字符;%
(百分号):表示零个、一个或多个字符。
其余字符则表示它们自身。
示例
-
检索以 "Jo" 开头的所有名字,例如
John
、Johanna
或Jonny W.
。由于%
也可以表示零个字符,因此名字为Jo
的行也会被检索到:SELECT * FROM person WHERE firstname LIKE 'Jo%';
-
检索与 "Meier" 类似的姓氏,例如
Meyer
或Maier
。__
表示恰好两个任意字符。不过可能会有意外结果,例如会检索到Miler
(但不会检索到Miller
):SELECT * FROM person WHERE lastname LIKE 'M__er';
转义字符
如果要检索 _
或 %
本身,需要使用转义机制。在模式中,用转义字符前缀这些特殊字符。例如:
-
检索包含下划线
_
的名称,如Electric_Motor
(但不包括Electric Motor
):SELECT * FROM parts WHERE partname LIKE 'Electric\_Motor' ESCAPE '\';
-
检索包含反斜杠
\
的名称,如Electric\Motor
(但不包括Electric Motor
):SELECT * FROM parts WHERE partname LIKE 'Electric\\Motor' ESCAPE '\';
转义字符可以是任何字符,不局限于反斜杠。例如:ESCAPE '!'
POSIX 语义
在过时的 SQL 标准中,曾经使用关键字 SIMILAR 来启用基于 POSIX 的模式匹配,但现在已弃用。
一些实现仍支持此功能,但使用不同的关键字,如 REGEXP、REGEXP_MATCHES、REGEXP_LIKE,或类似 ~
的运算符来启用正则匹配。
XQuery 语义
SQL 标准定义了关键字 LIKE_REGEX 来启用与 XQuery 1.0 / XPath 2.0 函数 fn:matches()
语义一致的模式匹配。
全文检索
SQL 标准通过 ISO/IEC 13249 的 SQL 多媒体与应用程序包进行了扩展,第 2 部分定义了针对文本文档的信息检索方法。这些方法支持操作对象如:单词、短语、句子和段落。
功能包括:
- 搜索单词或短语;
- 在可定义的距离内搜索单词;
- 使用同义词库功能,如词语的上位词;
- 声音相似度(Soundex);
- 结果排序等。
全文检索的核心方法是 CONTAINS。
示例
检索包含单词 word_1
和 word_2
(按此顺序)且二者在 10 个单词范围内的行:
SELECT *
FROM t1
WHERE CONTAINS(' "word_1" NEAR "word_2" WITHIN 10 WORDS IN ORDER ') = 1;
总结
- LIKE 提供了基本的模式匹配功能,适合简单字符串搜索。
- POSIX 和 XQuery 语义 提供更强大的正则匹配功能。
- 全文检索 通过 CONTAINS 方法支持更高级的信息检索,通常结合特殊文本索引实现。