Postgresql 中文操作指南

9.7. Pattern Matching #

PostgreSQL 提供了三种单独的方法用于模式匹配：传统的 SQL LIKE 运算符、较新的 SIMILAR TO 运算符（在 SQL:1999 中添加）以及 POSIX 风格的正则表达式。除了基本的“此字符串是否与此模式匹配？”运算符外，还有可用于提取或替换匹配子字符串以及在匹配位置拆分字符串的函数。

Tip

如果您有超出此范围的模式匹配需求，请考虑用 Perl 或 Tcl 编写一个用户自定义函数。

Caution

虽然大多数正则表达式搜索都可以非常快速地执行，但可以设计出处理时间和内存任意增加的正则表达式。对接受来自敌对来源的正则表达式搜索模式要保持警惕。如果必须这样做，建议实施语句超时。

使用_SIMILAR TO_ 模式进行搜索具有相同安全隐患，因为_SIMILAR TO_提供了许多功能，与 POSIX 样式的正则表达式相同。

LIKE 搜索比其他两个选项简单得多，因此在具有可能敌意的模式来源时使用起来更安全。

这三种模式匹配运算符都不支持非确定性排序规则。如果需要，应将不同的排序规则应用于表达式以解决此限制。

9.7.1. LIKE #

string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]

如果_string_与提供的_pattern_匹配，则_LIKE_ 表达式返回 true。（正如预期的那样，如果_LIKE_ 返回 true，则_NOT LIKE_ 表达式返回 false，反之亦然。等效表达式为 NOT (_string LIKE pattern_）。

如果 pattern 不包含百分号或下划线，则模式只表示字符串本身；在这种情况下， LIKE 的作用就像等式运算符。下划线 ( ) in _pattern 表示 (匹配) 任何单个字符；百分号 ( % ) 匹配任何 0 个或多个字符序列。

举例：

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

LIKE 模式匹配始终涵盖整个字符串。因此，如果要匹配字符串中的任意序列，则该模式必须以百分号开头和结尾。

要匹配不匹配其他字符的文字下划线或百分号，pattern 中的相应字符必须前缀有转义字符。默认转义字符是反斜杠，但可以使用_ESCAPE_ 子句选择不同的转义字符。要匹配转义字符本身，请编写两个转义字符。

Note

如果您已关闭 standard_conforming_strings，则在字符串常量中编写的任何反斜杠都需要加倍。有关更多信息，请参阅 Section 4.1.2.1。

也可以通过编写_ESCAPE ''_ 来选择没有转义字符。这会有效禁用转义机制，从而无法关闭模式中下划线和百分号的特殊含义。

根据 SQL 标准，省略_ESCAPE_意味着没有转义字符（而不是默认为反斜杠），并且不允许长度为零的_ESCAPE_ 值。因此，PostgreSQL 在这方面的行为稍微有点非标。

可以用_ILIKE_ 关键字代替_LIKE_，以根据活动区域设置使匹配对大小写不敏感。这不在 SQL 标准中，而是 PostgreSQL 的扩展。

~~ 运算符等同于 LIKE，而 ~~* 对应于 ILIKE。还有 !~~ 和 !~~* 运算符，它们分别表示 NOT LIKE 和 NOT ILIKE。所有这些运算符都是 PostgreSQL 特有的。你可能在 EXPLAIN 输出和类似的地方看到这些运算符名称，因为解析器实际上将 LIKE 等翻译为这些运算符。

在 PostgreSQL 语法中，LIKE、ILIKE、NOT LIKE 和 NOT ILIKE 短语通常被视为运算符；例如，它们可以用于 expression operator ANY (subquery) 构造中，尽管那里不能包含 ESCAPE 子句。在某些罕见的情况下，可能需要使用底层运算符名称。

另请参阅 starts-with 运算符 ^@ 和对应的 starts_with() 函数，它们在只需要匹配字符串开头的情况下很有用。

9.7.2. SIMILAR TO Regular Expressions #

string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]

SIMILAR TO 运算符根据其模式是否与给定的字符串匹配，返回 true 或 false。它类似于 LIKE，除了它使用 SQL 标准的正则表达式定义来解释模式。SQL 正则表达式是对 LIKE 表示法和通用 (POSIX) 正则表达式表示法的奇怪交叉。

与 LIKE 一样，SIMILAR TO 运算符仅在其模式与整个字符串匹配时才成功；这与常见的正则表达式行为不同，其中模式可以匹配字符串的任何部分。也像 LIKE 一样，SIMILAR TO 使用 _ 和 % 作为通配符，分别表示任何单个字符和任何字符串（它们可以与 POSIX 正则表达式中的 . 和 .* 相媲美）。

除了从 LIKE 借来的这些功能之外，SIMILAR TO 还支持从 POSIX 正则表达式中借来的这些模式匹配元字符：

请注意，句点 (.) 不是 SIMILAR TO 的元字符。

与 LIKE 一样，反斜杠会禁用这些元字符的任何特殊含义。可以使用 ESCAPE 指定不同的转义字符，或者通过编写 ESCAPE '' 来禁用转义功能。

另一个非标准扩展是，转义符后面跟着字母或数字会提供对 POSIX 正则表达式定义的转义序列的访问；请参阅下文的 Table 9.20、 Table 9.21 和 Table 9.22。

举例：

'abc' SIMILAR TO 'abc'          true
'abc' SIMILAR TO 'a'            false
'abc' SIMILAR TO '%(b|d)%'      true
'abc' SIMILAR TO '(b|c)%'       false
'-abc-' SIMILAR TO '%\mabc\M%'  true
'xabcy' SIMILAR TO '%\mabc\M%'  false

具有三个参数的 substring 函数提供提取与 SQL 正则表达式模式匹配的子字符串。可以按照标准 SQL 语法编写该函数：

substring(string similar pattern escape escape-character)

或使用现已过时的 SQL:1999 语法：

substring(string from pattern for escape-character)

或作为普通的三个参数函数：

substring(string, pattern, escape-character)

与 SIMILAR TO 一样，指定模式必须匹配整个数据字符串，否则函数将失败并返回 null。为了指示模式的哪一部分与匹配的数据子字符串相关，模式应包含转义字符后跟双引号 (") 的两个出现。当匹配成功时，将返回匹配模式中该部分文本的文本。

转义双引号分隔符实际上将 substring 的模式划分为三个独立的正则表达式；例如，三个部分中的任何部分中的竖线 (|) 都只影响该部分。此外，在关于有多少数据字符串与哪种模式匹配存在任何歧义时，这些正则表达式中的第一个和第三个被定义为匹配最小可能的文本量，而不是最大量。（在 POSIX 术语中，第一个和第三个正则表达式被迫成为非贪婪的。）

作为对 SQL 标准的扩展，PostgreSQL 允许只有一个转义双引号分隔符，在这种情况下，第三个正则表达式被视为为空；或者没有分隔符，在这种情况下，第一个和第三个正则表达式被视为为空。

一些示例，使用 #" 来分隔返回字符串：

substring('foobar' similar '%#"o_b#"%' escape '#')   oob
substring('foobar' similar '#"o_b#"%' escape '#')    NULL

9.7.3. POSIX Regular Expressions #

Table 9.16 列出了用于使用 POSIX 正则表达式进行模式匹配的可用运算符。

Table 9.16. Regular Expression Match Operators

Operator

Description

Example(s)

text ~ text → boolean 字符串区分大小写匹配正则表达式， 'thomas' ~ 't.*ma' → t

text ~ text → boolean 字符串不区分大小写匹配正则表达式， 'thomas' ~ 'T.*ma' → t

text !~ text → boolean 字符串区分大小写不匹配正则表达式， 'thomas' !~ 't.*max' → t

text !~ text → boolean 字符串不区分大小写不匹配正则表达式， 'thomas' !~ 'T.*ma' → f

与 LIKE 和 SIMILAR TO 操作符相比，POSIX 正则表达式在模式匹配中提供了更强大的手段。像 egrep、sed 或 awk 这样的许多 Unix 工具使用的模式匹配语言与此处描述的语言类似。

正则表达式是一个字符序列，是对字符串集的简要定义（一个 regular set）。字符串被认为与正则表达式匹配，如果它是正则表达式描述的正则集的成员。正则表达式与 LIKE 相同，模式字符与字符串字符完全匹配，除非它们是正则表达式语言中的特殊字符——但正则表达式使用与 LIKE 不同的特殊字符。与 LIKE 模式不同，正则表达式被允许与字符串中的任何位置匹配，除非正则表达式被明确地固定在字符串的开始或结尾。

举例：

'abcd' ~ 'bc'     true
'abcd' ~ 'a.c'    true — dot matches any character
'abcd' ~ 'a.*d'   true — * repeats the preceding pattern item
'abcd' ~ '(b|x)'  true — | means OR, parentheses group
'abcd' ~ '^a'     true — ^ anchors to start of string
'abcd' ~ '^(b|c)' false — would match except for anchoring

POSIX 模式语言在下面有更详细的描述。

substring 函数有两个参数，substring(_string（从 pattern 开始），提供了对匹配 POSIX 正则表达式模式的子串的提取。如果不存在匹配项，它返回 null，否则返回与模式匹配的文本的第一部分。但是，如果模式包含任何圆括号，则返回与第一个有括号的子表达式相匹配的文本部分（左括号在前）。如果你想在括号中使用括号而不触发此异常，你可以在整个表达式周围加上括号。如果你需要在要提取的子表达式之前的模式中使用括号，请参阅下面描述的非捕获括号。

举例：

substring('foobar' from 'o.b')     oob
substring('foobar' from 'o(.)b')   o

regexp_count 函数计算 POSIX 正则表达式模式与字符串匹配的位置数。其语法是 regexp_count(string, pattern [, start [, flags ]]). 通常，在 string 中从字符串的开头搜索_pattern_，但如果提供了 start 参数，则从该字符索引开始搜索。flags 参数是一个可选的文本字符串，其中包含零个或多个单字母标记，用于更改函数的行为。例如，在 flags 中包含 i 可以指定不区分大小写的匹配。支持的标记在 Table 9.24 中进行了描述。

举例：

regexp_count('ABCABCAXYaxy', 'A.')          3
regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  4

regexp_instr 函数返回 POSIX 正则表达式模式与字符串的第 N'次匹配的起始或结束位置，如果不存在此类匹配，则返回零。其语法是 regexp_instr(string, pattern [, start [, N [, endoption [, flags [, subexpr ]]]]]]). 通常，在 string 中从字符串的开头搜索_pattern_，但如果提供了 start 参数，则从该字符索引开始搜索。如果指定了 N，则会找到模式的第_N_'次匹配，否则会找到第一次匹配。如果省略 endoption 参数或指定为零，则函数将返回匹配的第一个字符位置。否则，endoption 必须为 1，并且函数将返回匹配后的字符位置。flags 参数是一个可选的文本字符串，其中包含零个或多个单字母标记，用于更改函数的行为。支持的标记在 Table 9.24 中进行了描述。对于包含括号子表达式的模式，subexpr 是一个整数，表示哪一个子表达式是感兴趣的：结果标识匹配该子表达式的子字符串的位置。子表达式按其左括号的顺序进行编号。当省略或将 subexpr 设置为零时，结果标识整个匹配位置，而不管是否存在括号子表达式。

举例：

regexp_instr('number of your street, town zip, FR', '[^,]+', 1, 2)
                                   23
regexp_instr('ABCDEFGHI', '(c..)(...)', 1, 1, 0, 'i', 2)
                                   6

regexp_like 函数检查 POSIX 正则表达式模式的匹配是否发生在字符串中，返回布尔 true 或 false。它的语法是 regexp_like(string, pattern [, flags ])。flags 参数是一个可选项文本串，包含零个或多个单字母标识，以更改此函数的行为。 Table 9.24 中描述了受支持标识。如果没有指定标识，则此函数具有与 ~ 运算符相同的结果。如果仅指定 i 标识，则该函数具有与 ~* 运算符相同的结果。

举例：

regexp_like('Hello World', 'world')       false
regexp_like('Hello World', 'world', 'i')  true

regexp_match 函数返回 POSIX 正则表达式模式针对一个字符串的首次匹配内匹配子串的文本数组。它的语法是 regexp_match(string, pattern [, flags ])。如果没有匹配，则结果为 NULL。如果找到匹配，并且 pattern 不包含任何带括号的子表达式，则结果为包含匹配整个模式的子串的单元素文本数组。如果找到匹配，并且 pattern 包含带括号的子表达式，则结果是文本数组，其 n 元素是匹配 pattern 的 n 带括号的子表达式的子串（不计“非捕获”括号；有关详细信息，请参见下文）。flags 参数是一个可选项文本串，包含零个或多个单字母标识，以更改此函数的行为。 Table 9.24 中描述了受支持标识。

举例：

SELECT regexp_match('foobarbequebaz', 'bar.*que');
 regexp_match
--------------
 {barbeque}
(1 row)

SELECT regexp_match('foobarbequebaz', '(bar)(beque)');
 regexp_match
--------------
 {bar,beque}
(1 row)

Tip

在只需提取整个匹配子字符串或 NULL 表示不匹配的情况下，最佳方法是使用 regexp_substr()。但是，regexp_substr() 仅存在于 PostgreSQL 版本 15 及更高版本中。在使用较旧版本时，你可以提取 regexp_match() 结果中的第一个元素，例如：

SELECT (regexp_match('foobarbequebaz', 'bar.*que'))[1];
 regexp_match
--------------
 barbeque
(1 row)

regexp_matches 函数返回 POSIX 正则表达式模式针对字符串的匹配内匹配子串的文本数组的集合。它具有与 regexp_match 相同的语法。如果没有任何匹配，则此函数不返回行；如果有一个匹配并且没有给出 g 标识，则返回一行；如果存在 N 个匹配并且给出了 g 标识，则返回 N 行。每个返回的行都是文本数组，其中包含整个匹配子串或匹配 pattern 的带括号的子表达式的子串，就像上面为 regexp_match 所描述的那样。regexp_matches 接受 Table 9.24 中显示的所有标识，加上命令它返回所有匹配（不仅仅是第一个匹配）的 g 标识。

举例：

SELECT regexp_matches('foo', 'not there');
 regexp_matches
----------------
(0 rows)

SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
 regexp_matches
----------------
 {bar,beque}
 {bazil,barf}
(2 rows)

Tip

在大多数情况下，regexp_matches() 应与 g 标志一起使用，因为如果你只想获取第一个匹配，使用 regexp_match() 更容易且更高效。但是，regexp_match() 仅存在于 PostgreSQL 版本 10 及更高版本中。在使用较旧版本时，一个常见的技巧是将 regexp_matches() 调用放入子选择中，例如：

SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;

如果存在匹配，则产生文本数组，否则 NULL，如同 regexp_match() 所做的一样。如果没有子选择，则对于没有匹配的表行，该查询完全不会产生任何输出，这通常不是期望的行为。

regexp_replace 函数提供替换符合 POSIX 正则表达式模式的子字符串的新文本。其语法为 regexp_replace ( source , pattern , replacement [, start [, N ]] [, flags ])。（请注意，必须同时指定 N 和 start ，但无论如何都可以指定 flags 。）如果没有与 pattern 匹配的项，则返回未更改 source 字符串。如果有匹配的项，则返回 source 字符串，其中 replacement 字符串替换了匹配的子字符串。 replacement 字符串可以包含 __n ，其中 n 为 1 至 9，以表示应插入与模式的 n 个带括号的子表达式匹配的源子字符串，并且可以包含 & 以指示应插入与整个模式相匹配的子字符串。如果您需要在替换文本中放置一个反斜杠文字，请写入 \\ 。通常从字符串的开头在 string 中搜索 pattern ，但如果提供了 start 参数，则从该字符索引位置开始搜索。默认情况下，只替换第一个与模式匹配的项。如果指定 N 且 N 大于 0，则替换第 N 个模式匹配项。如果指定 g 标志，或者指定 N 且为 0，则替换 start 位置或 start 位置之后的全部匹配项。（ g 标志在指定 N 时被忽略。） flags 参数是可选文本字符串，内含零个或多个可更改函数行为的单字母标志。 Table 9.24 中介绍了受支持的标志（不包括 g ）。

举例：

regexp_replace('foobarbaz', 'b..', 'X')
                                   fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
                                   fooXX
regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g')
                                   fooXarYXazY
regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 0, 'i')
                                   X PXstgrXSQL fXnctXXn
regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 3, 'i')
                                   A PostgrXSQL function

regexp_split_to_table 函数使用 POSIX 正则表达式模式作为分隔符来拆分字符串。它的语法是 regexp_split_to_table(string, pattern [, flags ])。如果没有 pattern 匹配，则函数返回 string。如果至少有一个匹配，则对于每个匹配，它返回从上次匹配的末尾（或字符串的开始）到本次匹配的开始的文本。当不再有匹配时，它返回从上次匹配的末尾到字符串末尾的文本。flags 参数是一个可选项文本串，包含零个或多个单字母标识，以更改此函数的行为。regexp_split_to_table 支持 Table 9.24 中描述的标识。

regexp_split_to_array 函数的行为与 regexp_split_to_table 相同，不同之处在于 regexp_split_to_array 将其结果作为 text 数组返回。它的语法是 regexp_split_to_array(string, pattern [, flags ])。这些参数与 regexp_split_to_table 的参数相同。

举例：

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
  foo
-------
 the
 quick
 brown
 fox
 jumps
 over
 the
 lazy
 dog
(9 rows)

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
              regexp_split_to_array
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
 foo
-----
 t
 h
 e
 q
 u
 i
 c
 k
 b
 r
 o
 w
 n
 f
 o
 x
(16 rows)

如最后一个示例所示，regexp split 函数忽略在字符串的开头或结尾或紧跟前一个匹配之后出现的零长度匹配。这与其他 regexp 函数实现的严格 regexp 匹配定义相反，但实际上通常是最方便的行为。Perl 等其他软件系统也使用类似的定义。

regexp_substr 函数返回匹配 POSIX 正则表达式模式的子串，或在没有匹配时返回 NULL。它的语法是 regexp_substr(string, pattern [, start [, N [, flags [, subexpr ]]]]).通常情况下， pattern 是从字符串的开头开始在 string 中搜索的，但是如果提供了 start 参数，则从此字符索引开始搜索。如果指定了 N，则返回模式的 N 匹配，否则返回第一个匹配。flags 参数是一个可选项文本串，包含零个或多个单字母标识，以更改此函数的行为。 Table 9.24 中描述了受支持标识。对于包含带括号的子表达式的模式，subexpr 是指定感兴趣子表达式的整数：结果是匹配该子表达式的子串。子表达式按其左括号的顺序进行编号。当省略 subexpr 或使其为零时，结果是整个匹配，而不管是否有带括号的子表达式。

举例：

regexp_substr('number of your street, town zip, FR', '[^,]+', 1, 2)
                                    town zip
regexp_substr('ABCDEFGHI', '(c..)(...)', 1, 1, 'i', 2)
                                   FGH

9.7.3.1. Regular Expression Details #

PostgreSQL 的正则表达式是使用 Henry Spencer 编写的软件包实现的。以下是正则表达式的描述，其中大部分直接从其手册中复制而来。

根据 POSIX 1003.2 定义的正则表达式（RE）有两种形式：extended RE 或 ERE（大致相当于 egrep）和 basic RE 或 BRE（大致相当于 ed）。PostgreSQL 支持这两种形式，还实现了一些不在 POSIX 标准中的扩展，但由于在 Perl 和 Tcl 等编程语言中可用而被广泛使用。使用这些非 POSIX 扩展的 RE 在本文档中称为 advanced RE 或 ARE。ARE 几乎是 ERE 的精确超集，但 BRE 存在多个记法不兼容（以及限制更多）。我们首先描述 ARE 和 ERE 形式，注意仅适用于 ARE 的功能，然后描述 BRE 的不同之处。

Note

PostgreSQL 最初总是假定正则表达式遵循 ARE 规则。但是，可以通过在 RE 模式前添加 _embedded option_来选择更有限的 ERE 或 BRE 规则，如 Section 9.7.3.4中所述。这对于与期望完全遵循 POSIX 1003.2 规则的应用程序兼容可能很有用。

正则表达式定义为一个或多个 branches，以 | 分隔。它匹配与其中一个分支相匹配的任何内容。

分支是零个或多个 quantified atoms 或 constraints，串联而成。它匹配第一个的匹配，后跟第二个的匹配，依此类推；空分支匹配空字符串。

量化的原子是一个 atom，后面可能跟一个 quantifier。如果没有量词，则它匹配一个原子匹配。如果有量词，则它可以匹配原子的多个匹配。atom 可以是 Table 9.17 中显示的任何可能性之一。可能的量词及其含义在 Table 9.18 中显示。

constraint 匹配空字符串，但只有在满足特定条件时才匹配。可以在原子可以使用的位置使用约束，但该约束不能后跟量词。 Table 9.19 中显示了简单的约束；稍后将描述更多约束。

Table 9.17. Regular Expression Atoms

Atom

Description

(re)

（其中 re 是任何正则表达式）匹配 re 的匹配项，为可能报告的匹配项做标记

(?:re)

如上所述，但匹配项未标记为报告（一组“非捕获”圆括号）（仅 ARE）

matches any single character

[chars]

一个 bracket expression ，匹配 chars 中的任何一个（有关更多详细信息，请参见 Section 9.7.3.2 ）

__k

（其中 k 是非字母数字字符）匹配该字符视为普通字符，例如： \\ 匹配反斜杠字符

__c

其中 c 为字母数字（后面可能跟有其他字符）是一个 escape ，请参见 Section 9.7.3.3 （仅限于 ARE；在 ERE 和 BRE 中，它匹配 c ）

{

当后面跟有非数字时，匹配左大括号字符 { ；当后面跟有数字时，表示 bound 的开头（见下文）

其中 x 是没有其他意义的单个字符，匹配该字符

RE 不能以反斜杠 (\) 结尾。

Note

如果您已关闭 standard_conforming_strings，则在字符串常量中编写的任何反斜杠都需要加倍。有关更多信息，请参阅 Section 4.1.2.1。

Table 9.18. Regular Expression Quantifiers

Quantifier

Matches

0 个或多个原子匹配序列

1 个或多个原子匹配序列

0 个或 1 个原子匹配序列

{m}

精确匹配原子 m 的序列

{m,}

匹配原子至少 m 次的序列

{m,n}

匹配原子 m 至 n （包括）之间的序列； m 不能超过 n

non-greedy version of *

non-greedy version of +

non-greedy version of ?

{m}?

non-greedy version of {m}

{m,}?

non-greedy version of {m,}

{m,n}?

non-greedy version of {m,n}

使用 {…} 的形式称为 bounds 。绑定中的数字 m 和 n 是无符号十进制整数，允许的值范围为 0 至 255（包括）。

Non-greedy 量词（仅在 ARE 中可用）与它们对应的普通 (greedy)对应项匹配的可能性相同，但优先选择最小的数字，而不是最大的数字。有关更多详细信息，请参见 Section 9.7.3.5。

Note

量词不能紧跟另一个量词，例如 ** 是无效的。量词不能位于表达式的开头或子表达式开头，也不能紧跟 ^ 或 |。

Table 9.19. Regular Expression Constraints

Constraint

Description

字符串开始处的匹配项

字符串末尾的匹配项

(?=re)

在 re 匹配的子字符串开始的任何位置处的 positive lookahead 匹配项（仅 ARE）

(?!re)

在没有 re 匹配的子字符串开始的任何位置处的 negative lookahead 匹配项（仅 ARE）

(?⇐re)

在 re 匹配的子字符串结束的任何位置处的 positive lookbehind 匹配项（仅 ARE）

(?<!re)

在没有 re 匹配的子字符串结束的任何位置处的 negative lookbehind 匹配项（仅 ARE）

先行和后续约束不能包含 back references（参见 Section 9.7.3.3），且它们内部的所有括号都视为非捕获。

9.7.3.2. Bracket Expressions #

bracket expression 是一个字符列表，由 [] 包括。它通常匹配列表中的任何单个字符（但请参见下文）。如果列表以 ^ 开头，它将匹配列表中其余的 not。如果列表中的两个字符由 - 分隔，则这表示整理序列中这两个字符（包括此二者）之间的字符的全部范围，，例如，ASCII 中的 [0-9] 匹配任何十进制数字。两个范围共享一个端点属于非法情况，例如 a-c-e。范围极度依赖排序序列，因此可移植程序应避免依赖于它们。

若要将文字 ] 包含在列表中，请将其设为第一个字符（在 ^ 之后，如果使用该字符）。若要包含文字 -，请将其设为第一个或最后一个字符，或范围的第二个端点。若要使用文字 - 作为范围的第一个端点，请将其包括在 [. 和 .] 中，使其成为排序元素（请参见下文）。除这些字符外，一些使用 [ 的组合（请参见下一段）和转义字符（仅限 ARE）外，所有其他特殊字符在括号表达式中都会失去其特殊意义。尤其是，按照 ERE 或 BRE 规则 \ 不是特殊字符，尽管在 ARE 中它很特殊（作为转义符）。

在括号表达式中，由 [. 和 .] 包含的排序元素（字符、整理为单个字符的多字符序列，或任一元素的排序序列名称）表示该排序元素字符的序列。该序列被视为括号表达式列表的单个元素。这样，包含多字符排序元素的括号表达式就可以匹配多个字符，例如，如果排序序列包括 ch 排序元素，则 RE [[.ch.]]*c 匹配 chchcc 的前五个字符。

Note

PostgreSQL 目前不支持多字符整理元素。此信息描述了可能的未来行为。

在括号表达式中，由 [= 和 =] 包含的排序元素是一个 equivalence class，它代表所有相当于该排序元素的排序元素（包括本身）构成的字符序列。（如果没有其他等效的排序元素，则处理方式与包络分隔符为 [. 和 .] 的情况相同。）例如，如果 o 和 ^ 是等价类别的成员，则 [[=o=]]、[[=^=]] 和 [o^] 是同义词。等价类别不能是范围的端点。

在括号表达式中，由 [: 和 :] 包含的字符类名称代表属于该类的所有字符的列表。字符类不能用作范围的端点。POSIX 标准定义了以下字符类名称：alnum（字母和数字）、alpha（字母）、blank（空格和制表符）、cntrl（控制字符）、digit（数字）、graph（空格除外的可打印字符）、lower（小写字母）、print（可打印字符，包括空格）、punct（标点）、space（任何空白）、upper（大写字母）、xdigit（十六进制数字）。这些标准字符类的行为通常在 7 位 ASCII 集中的字符在各个平台上是一致的。某个特定的非 ASCII 字符是否被认为属于其中一个类，这取决于用于正则表达式函数或运算符的 collation（请参见 Section 24.2），或者默认情况下取决于数据库的 LC_CTYPE 区域设置（请参见 Section 24.1）。即使在同名的区域设置中，非 ASCII 字符的分类也可能因平台而异。（但是，C 区域设置绝不会将任何非 ASCII 字符视为属于其中任何一个类。）除了这些标准字符类之外，PostgreSQL 定义了 word 字符类，它与 alnum 相同，此外还包含下划线 (_) 字符，以及 ascii 字符类，它只包含 7 位 ASCII 集。

括号表达式有两种特殊情况：括号表达式 [[:<:]] 和 [[:>:]] 是约束，分别匹配单词开头和结尾处的空字符串。单词定义为不以单词字符开头或结尾的单词字符序列。单词字符是属于 word 字符类的任何字符，即任何字母、数字或下划线。这是一个扩展，与 POSIX 1003.2 兼容但不被指定，并且应谨慎用于旨在移植到其他系统的软件中。下面描述的约束转义字符通常更可取；它们不更标准，但更容易键入。

9.7.3.3. Regular Expression Escapes #

Escapes 是以 \ 之后跟字母数字字符开头的特殊序列。转义有若干种类：字符输入、类简写、约束转义和反向引用。一个 \ 后跟一个字母数字字符但不是一个有效转义对于 ARE 是非法的。在 ERE 中，没有转义：在括号表达式外部，一个 \ 后跟一个字母数字字符仅仅代表该字符作为普通字符，而在括号表达式内部，\ 是一个普通字符。（后者正是 ERE 和 ARE 之间的实际差异。）

Character-entry escapes 的存在是为了更容易在 RE 中指定不可打印字符和其他不方便的字符。它们显示在 Table 9.20 中。

Class-shorthand escapes 提供了对某些常用字符类的简写。它们显示在 Table 9.21 中。

constraint escape 是一个约束，它匹配在满足特定条件时形成的空字符串，用转义字符写成。它们显示在 Table 9.22 中。

back reference ( __n ) 匹配由数字 n 指定的前一个圆括号子表达式匹配的字符串（请参阅 Table 9.23 ）。例如， ([bc])\1 匹配 bb 或 cc ，但不匹配 bc 或 cb 。子表达式必须完全位于正则表达式中的反向引用之前。子表达式按照其左括号的顺序进行编号。非捕获括号不定义子表达式。反向引用只考虑由引用的子表达式匹配的字符串字符，而不考虑其中包含的任何约束。例如， (^\d)\1 将匹配 22 。

Table 9.20. Regular Expression Character-Entry Escapes

Escape

Description

警报（响铃）字符，如在 C 中

backspace, as in C

反斜杠 \ 的同义词，有助于减少对反斜杠加倍的需求

\c__X

（其中 X 是任意字符）其低阶 5 位与 X 相同而其他所有位均为零的字符

排序序列名称为 ESC 的字符，如果没有则为八进制值为 033 的字符

换页符，如在 C 中

newline, as in C

回车符，如在 C 中

水平制表符，如在 C 中

\u__wxyz

（其中 wxyz 正好是四个十六进制数字）其十六进制值为 0x__wxyz 的字符

\U__stuvwxyz

（其中 stuvwxyz 精确为八个十六进制数字）十六进制值为 0x__stuvwxyz 的字符

垂直制表符，如 C

\x__hhh

（其中 hhh 为任何十六进制数字序列）十六进制值为 0x__hhh 的字符（无论使用多少个十六进制数字，均为单个字符）

值 0 的字符（空字节）

__xy

（其中 xy 恰好为两个八进制数字，且不是 back reference ）八进制值为 0__xy 的字符

__xyz

（其中 xyz 恰好为三个八进制数字，且不是 back reference ）八进制值为 0__xyz 的字符

十六进制数字为 0-9、a-f 和 A-F。八进制数字为 0-7。

指定 ASCII 范围 (0-127) 之外的值的数字字符输入转义的含义依赖于数据库编码。当编码为 UTF-8 时，转义值等同于 Unicode 代码点，例如 \u1234 表示字符 U+1234。对于其他多字节编码，字符输入转义通常仅指定字符的字节值的串联。如果转义值与数据库编码中的任何合法字符都不对应，则不会引发错误，但它永远不会匹配任何数据。

字符项转义始终被视为普通字符。例如，\135 在 ASCII 中是 ]，但 \135 不终止括号表达式。

Table 9.21. Regular Expression Class-Shorthand Escapes

Escape

Description

匹配任何数字，如 [id=":digit:"]

匹配任何空白字符，如 [id=":space:"]

匹配任何单词字符，如 [id=":word:"]

匹配任何非数字，如 [^[:digit:]]

匹配任何非空白字符，如 [^[:space:]]

匹配任何非单词字符，如 [^[:word:]]

类简写转义字符在括号表达式中也能用，尽管上面显示的定义在该上下文中在语法上并不是完全有效的。例如，[a-c\d] 等同于 [a-c[:digit:]]。

Table 9.22. Regular Expression Constraint Escapes

Escape

Description

仅匹配字符串开头（请参阅 Section 9.7.3.5 了解其与 ^ 的区别）

仅匹配单词开头

仅匹配单词结尾

仅匹配单词开头或结尾

仅匹配不是单词开头或结尾的点

仅匹配字符串结尾（请参阅 Section 9.7.3.5 了解其与 $ 的区别）

单词的定义如上文中的 [[:<:]] 和 [[:>:]] 中的规范。括号表达式中约束转义是非法的。

Table 9.23. Regular Expression Back References

Escape

Description

__m

（其中 m 为非零数字）对第 m 个子表达式的反向引用

__mnn

（ m 为一个非零数字，而 nn 为其他一些数字，且十进制值 mnn 不大于到目前为止看到的封闭捕获括号的数量）对第 mnn 个子表达式的反向引用

Note

八进制字符输入转义和反向引用之间存在固有模糊性，这是由以下启发式解决的，如上所述。前导零始终表示八进制转义。单个非零数字，后跟没有其他数字，始终视为反向引用。非零开头的多位数字序列在其后是适当子表达式（即该数字在反向引用的合法范围内）的情况下视为反向引用，否则视为八进制。

9.7.3.4. Regular Expression Metasyntax #

除了上面描述的主语法以外，还有某些特殊形式和各种语法工具可用。

RE 可以从两个 director 前缀之一开始。如果 RE 以 *:, the rest of the RE is taken as an ARE. (This normally has no effect in PostgreSQL, since REs are assumed to be AREs; but it does have an effect if ERE or BRE mode had been specified by the flags parameter to a regex function.) If an RE begins with *= 开头，则 RE 的其余部分将视为文字字符串，其中所有字符都被视为常规字符。

ARE 可以以 embedded options 开头：一个序列 (?_xyz)_ （其中 xyz 为一个或多个字母字符）指定影响正则表达式 (RE) 其余部分的选项。这些选项将覆盖任何先前确定的选项——特别是它们可以覆盖正则表达式运算符隐含的大小写敏感行为或正则表达式函数的 flags 参数。可用选项字母显示在 Table 9.24 中。请注意，这些相同的选项字母在正则表达式函数的 flags 参数中使用。

Table 9.24. ARE Embedded-Option Letters

Option

Description

RE 的其余部分是一个 BRE

区分大小写匹配（覆盖运算符类型）

RE 的其余部分是一个 ERE

不区分大小写匹配（参见 Section 9.7.3.5 ）（覆盖运算符类型）

historical synonym for n

newline-sensitive matching (see Section 9.7.3.5)

部分区分换行符敏感匹配（参见 Section 9.7.3.5 ）

RE 的其余部分是一个文字（“引用的”）字符串，所有普通字符

non-newline-sensitive matching (default)

紧凑语法（默认；见下文）

反部分区分换行符敏感（“奇怪的”）匹配（参见 Section 9.7.3.5 ）

expanded syntax (see below)

嵌入式选项在序列结束时生效 )。它们只能出现在 ARE 的开头（如果有的话，在 ***: 之后）。

除了通常的 (tight) RE 语法（其中所有字符都重要），还有一个 expanded 语法，可以通过指定嵌入式 x 选项来获取。在展开的语法中，RE 中的空格字符将被忽略，并且 # 和以下换行符（或 RE 的末尾）之间的所有字符也将被忽略。这允许对复杂的 RE 进行段落和注释。基本规则有三个例外：

出于这个目的，空格字符是空白、制表符、换行符和属于 space 字符类的任何字符。

最后，在 ARE 中，在方括号表达式外部，序列 (?#_ttt)_ （其中 ttt 是任何不包含 ) 的文本）是一个注释，完全忽略。同样，这在多字符符号的字符之间不被允许，如 (?: 。此类注释更多是一个历史文物，而不是一个有用的工具，不建议使用；改用扩展语法。

None 如果一个初始 ***= 导向已经指定将用户的输入视为一个文字字符串而不是 RE，这些元语法扩展的可用性将是有效的。

9.7.3.5. Regular Expression Matching Rules #

如果 RE 识别給定字符串中超过一个子字符串，则 RE 匹配字符串中最先开始的子字符串。如果 RE 匹配以该点开始超过一个子字符串，则根据 RE 是 greedy 还是 non-greedy，将采用最长可能的匹配或最短可能的匹配。

RE 是贪婪的还是非贪婪的由以下规则确定：

上述规则不仅将贪婪属性与单独的量化原子关联，还将贪婪属性与包含量化原子的分支和整个 RE 关联。这意味着匹配是以分支或整个 RE 匹配最长或最短可能的子字符串的方式完成的 as a whole。一旦确定整个匹配的长度，根据该子表达式的贪婪属性确定与任何特定子表达式匹配的部分，RE 中较早开始的子表达式比较后开始的子表达式优先。

这是表示其含义的一个示例：

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
Result: 123
SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
Result: 1

在第一个案例中，由于 Y* 是贪婪的，所以整个 RE 是贪婪的。它可以从 Y 开始匹配，并且匹配从那里开始的最长可能的字符串，即 Y123。输出是该字符串的圆括号部分，或 123。在第二个案例中，由于 Y*? 是非贪婪的，所以整个 RE 是非贪婪的。它可以从 Y 开始匹配，并且匹配从那里开始的最短可能的字符串，即 Y1。子表达式 [0-9]{1,3} 是贪婪的，但它不能改变对整体匹配长度的决策；因此它被迫仅匹配 1。

总之，当 RE 同时包含贪婪和非贪婪子表达式时，根据分配给整个 RE 的属性，总匹配长度尽可能长或尽可能短。分配给子表达式的属性仅影响相对于彼此她们可以“吃掉”多少那个匹配。

定量词 {1,1} 和 {1,1}? 可分别用于强制子表达式或整个正则表达式的贪婪或非贪婪。这在你需要让整个正则表达式具有不同于从其元素推导出的贪婪属性时很有用。例如，假设我们尝试将包含一些数字的字符串分离为数字及之前和之后的各部分。我们可以尝试像这样：

SELECT regexp_match('abc01234xyz', '(.*)(\d+)(.*)');
Result: {abc0123,4,xyz}

这不起作用：第一个 .* 是贪婪的，因此它会“吃掉”它所能吃掉的所有内容，让 \d+ 匹配在最后一个可能的位置，即最后一个数字。我们可以尝试通过使其为非贪婪来修复：

SELECT regexp_match('abc01234xyz', '(.*?)(\d+)(.*)');
Result: {abc,0,""}

这也不起作用，因为现在整个正则表达式都是非贪婪的，因此它会尽快结束总体匹配。我们可以强制整个正则表达式为贪婪来得到我们想要的东西：

SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
Result: {abc,01234,xyz}

分开控制正则表达式的总体贪婪与它的各个组件的贪婪，可让我们能够高度灵活地处理可变长度模式。

决定什么是一个较长或较短的匹配时，匹配长度以字符衡量，不是按排序元素衡量。空字符串被认为比根本不匹配更长。例如：bb* 匹配 abbbc 的中间三个字符；(week|wee)(night|knights) 匹配 weeknights 的所有十个字符；当 (.).* is matched against abc the parenthesized subexpression matches all three characters; and when (a)* 与 bc 匹配时，整个正则表达式和带括号的子表达式都匹配空字符串。

如果指定不区分大小写的匹配，其效果就好像全部大小写区别都已从字母中消失一样。当作为普通字符出现在方括号表达式外的存在多个大小写的字母时，它将有效地转换为包含这两个大小写的方括号表达式，例如，x_变为[xX]。当其出现在方括号表达式内时，它的所有大小写对应项都将添加到方括号表达式中，例如，[x]变为[xX]，而[^x]变为[xX]_。

如果指定了区分换行符的匹配，则_.和使用^的方括号表达式将绝不会匹配换行符（因此，除非 RE 明确包括换行符，否则匹配将不会越过行），且^和$将在换行符后和换行符前匹配空字符串，除了分别在字符串的开头和结尾进行匹配。但 ARE 转义字符\A_和_\Z_继续匹配字符串_only_的开头或结尾。此外，无论此模式如何，字符类别速记符号_\D_和_\W_都将匹配换行符。（在 PostgreSQL 14 之前，它们在区分换行符的模式中不匹配换行符。编写_[^{[:digit:]]或[}[:word:]]_可获得旧行为。）

如果指定部分区分换行符的匹配，这会影响 . 和使用 ^ 的括号表达式，与区分换行符的匹配一致，但不会影响 ^ 和 $。

如果指定逆部分区分换行符的匹配，这会影响 ^ 和 $，与区分换行符的匹配一致，但不会影响 . 和括号表达式。这不是很有用，但出于对称性的考虑还是提供了它。

9.7.3.6. Limits and Compatibility #

在此实现中，没有对正则表达式的长度施加任何特定的限制。但是，旨在高度可移植的程序不应采用长于 256 字节的正则表达式，因为兼容 POSIX 的实现可能会拒绝接受此类正则表达式。

ARE 的唯一实际与 POSIX ERE 不兼容的特性是 \ 在括号表达式中没有失去它的特殊含义。所有其他 ARE 特性都使用在 POSIX ERE 中是非法或具有未定义或未指定效果的语法；*** 指导器的语法同样也不属于 BRE 或 ERE 的 POSIX 语法。

许多 ARE 扩展从 Perl 借用而来，但其中一些已被更改以清理它们，并且一些 Perl 扩展不存在。值得注意的不兼容性包括 \b、\B、对尾随换行符缺乏特殊处理、对受区分换行符匹配影响的事物增加了补集括号表达式、对前瞻/后顾约束中的括号和反向引用的限制，以及最长/最短匹配（而不是首次匹配）匹配语义。

9.7.3.7. Basic Regular Expressions #

BRE 与 ERE 在几个方面存在差异。在 BRE 中，|、+_和?是普通字符，并且没有等效的功能。边界的分隔符为\{和\}_，而_{和}_本身是普通字符。嵌套子表达式的圆括号为_$和$，而(和)本身是普通字符。^是普通字符，但 RE 的开头或带括号的子表达式的开头除外，$是普通字符，但 RE 的结尾或带括号的子表达式的结尾除外，而*是普通字符，如果它出现在 RE 的开头或带括号的子表达式的开头（可能在^开头之后）。最后，可以使用个位数反向引用，并且<_和_>_分别为_[[:<:]]和[[:>:]]_的同义词；在 BRE 中没有其他转义字符可用。

9.7.3.8. Differences from SQL Standard and XQuery #

自 SQL:2008 起，SQL 标准包括根据 XQuery 正则表达式标准执行模式匹配的正则表达式运算符和函数：

PostgreSQL 目前不实现这些运算符和函数。你可以在每种情况下获得近似相等的函数，如 Table 9.25中所示。（此表已省略了双方的各种可选从句。）

Table 9.25. Regular Expression Functions Equivalencies

SQL standard

PostgreSQL

string_ LIKE_REGEX _pattern

regexp_like(_string , pattern )_ 或 string ~ pattern

OCCURRENCES_REGEX(_pattern IN string)_

regexp_count(_string, pattern)_

POSITION_REGEX(_pattern IN string)_

regexp_instr(_string, pattern)_

SUBSTRING_REGEX(_pattern IN string)_

regexp_substr(_string, pattern)_

TRANSLATE_REGEX(_pattern IN string WITH replacement )_

regexp_replace(_string, pattern, replacement)_

PostgreSQL 提供的正则表达式函数在许多其他 SQL 实现中也可以使用，而 SQL 标准函数的实现并不像它们那样广泛。正则表达式语法的一些细节可能因每个实现而异。

SQL 标准运算符和函数使用 XQuery 正则表达式，它与上面描述的 ARE 语法非常接近。现有的基于 POSIX 的正则表达式特性与 XQuery 正则表达式之间的显著差别包括：