自 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 谓词将类型为 CHARVARCHAR(字符串)的列与一个模式进行比较。模式也是一个字符串,可以包含两种具有特殊含义的字符:

  • _(下划线):表示任意 一个 字符;
  • %(百分号):表示零个、一个或多个字符。

其余字符则表示它们自身。

示例

  1. 检索以 "Jo" 开头的所有名字,例如 JohnJohannaJonny W.。由于 % 也可以表示零个字符,因此名字为 Jo 的行也会被检索到:

    SELECT * 
    FROM   person
    WHERE  firstname LIKE 'Jo%';
    
  2. 检索与 "Meier" 类似的姓氏,例如 MeyerMaier__ 表示恰好两个任意字符。不过可能会有意外结果,例如会检索到 Miler(但不会检索到 Miller):

    SELECT * 
    FROM   person
    WHERE  lastname LIKE 'M__er';
    

转义字符

如果要检索 _% 本身,需要使用转义机制。在模式中,用转义字符前缀这些特殊字符。例如:

  1. 检索包含下划线 _ 的名称,如 Electric_Motor(但不包括 Electric Motor):

    SELECT * 
    FROM   parts
    WHERE  partname LIKE 'Electric\_Motor' ESCAPE '\';
    
  2. 检索包含反斜杠 \ 的名称,如 Electric\Motor(但不包括 Electric Motor):

    SELECT * 
    FROM   parts
    WHERE  partname LIKE 'Electric\\Motor' ESCAPE '\';
    

转义字符可以是任何字符,不局限于反斜杠。例如:ESCAPE '!'


POSIX 语义

在过时的 SQL 标准中,曾经使用关键字 SIMILAR 来启用基于 POSIX 的模式匹配,但现在已弃用。

一些实现仍支持此功能,但使用不同的关键字,如 REGEXPREGEXP_MATCHESREGEXP_LIKE,或类似 ~ 的运算符来启用正则匹配。


XQuery 语义

SQL 标准定义了关键字 LIKE_REGEX 来启用与 XQuery 1.0 / XPath 2.0 函数 fn:matches() 语义一致的模式匹配。


全文检索

SQL 标准通过 ISO/IEC 13249 的 SQL 多媒体与应用程序包进行了扩展,第 2 部分定义了针对文本文档的信息检索方法。这些方法支持操作对象如:单词、短语、句子和段落。

功能包括:

  • 搜索单词或短语;
  • 在可定义的距离内搜索单词;
  • 使用同义词库功能,如词语的上位词;
  • 声音相似度(Soundex);
  • 结果排序等。

全文检索的核心方法是 CONTAINS

示例

检索包含单词 word_1word_2(按此顺序)且二者在 10 个单词范围内的行:

SELECT * 
FROM   t1
WHERE  CONTAINS(' "word_1" NEAR "word_2" WITHIN 10 WORDS IN ORDER ') = 1;

总结

  1. LIKE 提供了基本的模式匹配功能,适合简单字符串搜索。
  2. POSIX 和 XQuery 语义 提供更强大的正则匹配功能。
  3. 全文检索 通过 CONTAINS 方法支持更高级的信息检索,通常结合特殊文本索引实现。
最后修改: 2025年01月28日 星期二 14:09