PostgreSQL 教程

Appearance

9.7. 模式匹配# ​

9.7.1. LIKE
9.7.2. SIMILAR TO 正则表达式
9.7.3. 正则表达式

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

提示

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

警告 ​

虽然大多数正则表达式搜索可以非常快速地执行，但可以设计出需要任意时间和内存来处理的正则表达式。请小心接受来自敌对来源的正则表达式搜索模式。如果您必须这样做，建议设置语句超时。

使用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的作用类似于等号运算符。pattern*中的下划线 (_) 代表（匹配）任何单个字符；百分号 (%) 匹配任何零个或多个字符的序列。

一些示例

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

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

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

注意

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

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

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

关键字ILIKE可用于代替LIKE，以根据活动区域设置使匹配不区分大小写。这不在SQL标准中，而是PostgreSQL扩展。

运算符~~等效于LIKE，而~~*对应于ILIKE。还有!~~和!~~*运算符，分别表示NOT LIKE和NOT ILIKE。所有这些运算符都是PostgreSQL特有的。您可能会在EXPLAIN输出和类似位置看到这些运算符名称，因为解析器实际上会将LIKE等转换为这些运算符。

短语LIKE、ILIKE、NOT LIKE和NOT ILIKE通常在PostgreSQL语法中被视为运算符；例如，它们可用于*expression**operatorANY (subquery*) 构造中，尽管无法在其中包含ESCAPE子句。在某些不明显的情况下，可能需要改用底层运算符名称。

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

9.7.2.SIMILAR TO正则表达式# ​

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

SIMILAR TO运算符根据其模式是否与给定字符串匹配返回真或假。它类似于LIKE，不同之处在于它使用 SQL 标准对正则表达式的定义来解释模式。SQL 正则表达式是LIKE符号和常见（POSIX）正则表达式符号之间的奇怪交叉。

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

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

  • | 表示交替（两个备选方案中的任何一个）。

  • * 表示重复前一项零次或多次。

  • + 表示重复前一项一次或多次。

  • ? 表示前一个项目重复零次或一次。

  • {m} 表示前一个项目重复 m 次。

  • {m,} 表示前一个项目重复 m 次或更多次。

  • {m,n} 表示前一个项目至少重复 m 次，不超过 n 次。

  • 括号 () 可用于将项目分组为单个逻辑项目。

  • 方括号表达式 [...] 指定一个字符类，就像在 POSIX 正则表达式中一样。

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

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

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

另一个非标准扩展是，在转义字符后跟一个字母或数字，可以访问为 POSIX 正则表达式定义的转义序列；请参见下文的表 9.20、表 9.21和表 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正则表达式# ​

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

表 9.16. 正则表达式匹配运算符

运算符

说明

示例

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

POSIX正则表达式提供了一种比LIKE和SIMILAR TO运算符更强大的模式匹配方法。许多 Unix 工具（例如egrep、sed或awk）使用与此处描述的类似的模式匹配语言。

正则表达式是一个字符序列，是对一组字符串（正则集）的缩写定义。如果一个字符串是正则表达式描述的正则集的成员，则称该字符串与正则表达式匹配。与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*from*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指定不区分大小写的匹配。支持的标志在表 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必须为一，并且该函数返回匹配项后一个字符的位置。flags参数是一个可选文本字符串，其中包含零个或多个单字母标志，用于更改函数的行为。支持的标志在表 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*参数是一个可选文本字符串，其中包含零个或多个单字母标志，用于更改函数的行为。支持的标志在表 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'th 元素是与pattern的n'th 带括号的子表达式匹配的子字符串（不计入“非捕获”括号；有关详细信息，请参见下文）。flags*参数是一个可选文本字符串，其中包含零个或多个单字母标志，这些标志会更改函数的行为。受支持的标志在表 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)

提示

在通常情况下，您只需要整个匹配子字符串或没有匹配项时为NULL，最佳解决方案是使用regexp_substr()。但是，regexp_substr()仅存在于PostgreSQL15 及更高版本中。在使用较旧版本时，您可以提取regexp_match()结果的第一个元素，例如

函数regexp_matches返回一个文本数组集，其中包含与字符串中 POSIX 正则表达式模式的匹配项匹配的子字符串。其语法与regexp_match相同。如果没有匹配项，则此函数不返回任何行；如果有一个匹配项且未给出g标志，则返回一行；如果存在*N个匹配项且给出了g标志，则返回N行。每个返回的行都是一个文本数组，其中包含整个匹配子字符串或与pattern*的带括号的子表达式匹配的子字符串，如上文针对regexp_match所述。regexp_matches接受表 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)

提示

在大多数情况下，regexp_matches()应与g标志一起使用，因为如果您只想要第一个匹配项，则使用regexp_match()更容易、更高效。但是，regexp_match()仅存在于PostgreSQL10 及更高版本中。在使用较旧版本时，一个常见的技巧是将regexp_matches()调用放在子选择中，例如如果存在匹配项，则会生成一个文本数组，如果不存在，则生成NULL，与regexp_match()的行为相同。如果没有子选择，则此查询对于没有匹配项的表行将不会产生任何输出，这通常不是期望的行为。

Theregexp_replacefunction provides substitution of new text for substrings that match POSIX regular expression patterns. It has the syntaxregexp_replace(source,pattern,replacement[,start[,N]] [,flags]). (Notice that*Ncannot be specified unlessstartis, butflagscan be given in any case.) Thesourcestring is returned unchanged if there is no match to thepattern. If there is a match, thesourcestring is returned with thereplacementstring substituted for the matching substring. Thereplacementstring can contain\n, wherenis 1 through 9, to indicate that the source substring matching then'th parenthesized subexpression of the pattern should be inserted, and it can contain\&to indicate that the substring matching the entire pattern should be inserted. Write\\if you need to put a literal backslash in the replacement text.patternis searched for instring, normally from the beginning of the string, but if thestartparameter is provided then beginning from that character index. By default, only the first match of the pattern is replaced. IfNis specified and is greater than zero, then theN'th match of the pattern is replaced. If thegflag is given, or ifNis specified and is zero, then all matches at or after thestartposition are replaced. (Thegflag is ignored whenNis specified.) Theflags*parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Supported flags (though notg) are described inTable 9.24.

一些示例

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支持表 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)

正如最后一个示例所示，正则表达式拆分函数会忽略在字符串开头或结尾或紧接在上一个匹配项之后发生的零长度匹配项。这与其他正则表达式函数实现的正则表达式匹配的严格定义相反，但在实践中通常是最方便的行为。Perl 等其他软件系统也使用类似的定义。

函数regexp_substr返回与 POSIX 正则表达式模式匹配的子字符串，如果没有匹配，则返回NULL。其语法为regexp_substr(string,pattern[,start[,N[,flags[,subexpr]]]]). 通常从字符串开头在*string中搜索pattern，但如果提供了start参数，则从该字符索引开始。如果指定了N，则返回模式的第N个匹配，否则返回第一个匹配。flags参数是一个可选文本字符串，其中包含零个或多个单字母标志，用于更改函数的行为。支持的标志在表 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. 正则表达式详细信息# ​

PostgreSQL的正则表达式是使用 Henry Spencer 编写的软件包实现的。下面对正则表达式的描述大部分是从他的手册中逐字复制的。

正则表达式 (REs)，如POSIX1003.2 中定义的，有两种形式：扩展REs 或EREs（大致与egrep相同），以及基本REs 或BREs（大致与ed相同）。PostgreSQL支持这两种形式，还实现了一些不在 POSIX 标准中的扩展，但由于在 Perl 和 Tcl 等编程语言中可用，因此被广泛使用。使用这些非 POSIX 扩展的REs 在本文档中称为高级REs 或AREs。ARE 几乎是 ERE 的一个精确超集，但 BRE 有几个符号不兼容（以及限制更多）。我们首先描述 ARE 和 ERE 形式，注意仅适用于 ARE 的特性，然后描述 BRE 的不同之处。

注意

PostgreSQL始终最初假定正则表达式遵循 ARE 规则。但是，可以通过在 RE 模式前加上一个嵌入式选项来选择更有限的 ERE 或 BRE 规则，如第 9.7.3.4 节中所述。这对于与完全符合POSIX1003.2 规则的应用程序兼容很有用。

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

一个分支是零个或多个量化原子或约束，串联在一起。它匹配第一个的匹配，然后是第二个的匹配，依此类推；一个空分支匹配空字符串。

一个量化原子是一个原子，后面可能跟着一个量词。如果没有量词，它匹配原子的匹配。有了量词，它可以匹配原子的某些数量的匹配。原子可以是表 9.17中显示的任何可能性。可能的量词及其含义显示在表 9.18中。

一个约束匹配一个空字符串，但仅在满足特定条件时才匹配。约束可以用在原子可以使用的位置，但它后面不能跟着量词。简单的约束显示在表 9.19中；一些更多的约束将在后面描述。

表 9.17. 正则表达式原子

原子说明
(re)（其中re是任何正则表达式）匹配re的匹配，并为可能的报告记录匹配
(?:re)同上，但匹配不记录报告（一组“非捕获”括号）（仅 ARE）
.匹配任何单个字符
[chars]一个方括号表达式，匹配chars中的任何一个（有关更多详细信息，请参见第 9.7.3.2 节）
\k（其中k是非字母数字字符）匹配作为普通字符的该字符，例如，\\匹配反斜杠字符
\c其中 c 是字母数字（可能后跟其他字符）是 转义，请参见 第 9.7.3.3 节（仅 ARE；在 ERE 和 BRE 中，这匹配 c）
{当后跟非数字字符时，匹配左大括号字符 {；当后跟数字时，它是 bound 的开头（见下文）
x其中 x 是不具有其他意义的单个字符，匹配该字符

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

注意

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

表 9.18. 正则表达式量词

量词匹配
*0 个或更多原子匹配序列
+1 个或更多原子匹配序列
?0 个或 1 个原子匹配序列
{m}恰好 m 个原子匹配序列
{m,}m 个或更多原子匹配序列
{m,n}m 到 n（包括）个原子匹配序列；m 不能超过 n
*?* 的非贪婪版本
+?+ 的非贪婪版本
??? 的非贪婪版本
{m}?{m} 的非贪婪版本
{m,}?{m,} 的非贪婪版本
{m,n}?{m,n} 的非贪婪版本

使用{...}的形式称为边界。边界内的数字*m和n*是无符号十进制整数，允许的值为 0 到 255（包括）。

非贪婪量词（仅在 ARE 中可用）与相应的正常（贪婪）对应量词匹配相同的可能性，但优先选择最少数量的匹配，而不是最大数量的匹配。有关更多详细信息，请参阅第 9.7.3.5 节。

注意

量词不能紧跟另一个量词，例如，**是无效的。量词不能开始表达式或子表达式，也不能跟在^或|之后。

表 9.19. 正则表达式约束

约束说明
^匹配字符串开头
$匹配字符串结尾
(?=re)正向先行断言 匹配在任何匹配子字符串 re 开始的点（仅限 ARE）
(?!re)负向先行断言 匹配在任何没有匹配子字符串 re 开始的点（仅限 ARE）
(?<=re)正向后行断言 匹配在任何匹配子字符串 re 结束的点（仅限 ARE）
(?<!re)负向后行断言 匹配在任何没有匹配子字符串 re 结束的点（仅限 ARE）

先行断言和后行断言约束不能包含反向引用（请参阅第 9.7.3.3 节），并且其中的所有括号都被视为非捕获括号。

9.7.3.2. 方括号表达式# ​

方括号表达式是用[]括起来的一系列字符。它通常匹配列表中的任何单个字符（但请参见下文）。如果列表以^开头，则它匹配列表其余部分中不存在的任何单个字符。如果列表中的两个字符用-分隔，则这是对照序序列中这两个字符（包括）之间的所有字符范围的简写，例如，ASCII中的[0-9]匹配任何十进制数字。两个范围共享一个端点是非法的，例如，a-c-e。范围非常依赖于照序序列，因此可移植程序应避免依赖于它们。

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

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

注意

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

在括号表达式中，用[=和=]括起来的整理元素是一个等价类，表示与该元素等价的所有整理元素的字符序列，包括它本身。（如果没有其他等价整理元素，则处理方式如同封闭定界符为[.和.]。）例如，如果o和^是等价类的成员，则[[=o=]]、[[=^=]]和[o^]都是同义词。等价类不能作为范围的端点。

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

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

9.7.3.3. 正则表达式转义# ​

转义是以\开头的特殊序列，后跟一个字母数字字符。转义有几种类型：字符输入、类速记、约束转义和反向引用。\后跟一个字母数字字符但不是有效的转义在 ARE 中是非法的。在 ERE 中，没有转义：在括号表达式之外，\后跟一个字母数字字符仅仅表示该字符作为一个普通字符，而在括号表达式内，\是一个普通字符。（后者是 ERE 和 ARE 之间唯一实际的不兼容性。）

字符输入转义的存在是为了在正则表达式中更轻松地指定非打印和其他不方便的字符。它们显示在表 9.20中。

类简写转义为某些常用的字符类提供简写。它们显示在表 9.21中。

约束转义是一种约束，如果满足特定条件，则匹配空字符串，写为转义。它们显示在表 9.22中。

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

表 9.20。正则表达式字符输入转义

转义说明
\a警报（铃声）字符，如 C 中
\b退格，如 C 中
\B反斜杠（\）的同义词，有助于减少反斜杠加倍的需要
\cX（其中X 是任何字符）低阶 5 位与X 相同且其他位均为零的字符
\e排序序列名称为 ESC 的字符，或者如果没有，则八进制值为 033 的字符
\f换页符，如 C 中
\n换行符，如 C 中
\r回车符，如 C 中
\t水平制表符，如 C 中
\uwxyz（其中wxyz 正好是四个十六进制数字）十六进制值为 0xwxyz 的字符
\Ustuvwxyz（其中 stuvwxyz 正好是八个十六进制数字）十六进制值为 0xstuvwxyz 的字符
\v垂直制表符，如 C 中
\xhhh（其中 hhh 是任何十六进制数字序列）十六进制值为 0xhhh 的字符（无论使用多少个十六进制数字，都只有一个字符）
\0值为 0（空字节）的字符
\xy（其中 xy 正好是两个八进制数字，且不是 反向引用）八进制值为 0xy 的字符
\xyz（其中 xyz 正好是三个八进制数字，且不是 反向引用）八进制值为 0xyz 的字符

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

指定 ASCII 范围（0-127）之外值的数字字符转义的含义取决于数据库编码。当编码为 UTF-8 时，转义值等效于 Unicode 代码点，例如\u1234表示字符U+1234。对于其他多字节编码，字符转义通常只指定字符的字节值的连接。如果转义值不对应于数据库编码中的任何合法字符，则不会引发错误，但它永远不会匹配任何数据。

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

表 9.21 正则表达式类简写转义

转义说明
\d匹配任何数字，如 [[:digit:]]
\s匹配任何空白字符，如 [[:space:]]
\w匹配任何单词字符，如 [[:word:]]
\D匹配任何非数字，如 [^[:digit:]]
\S匹配任何非空白字符，如 [^[:space:]]
\W匹配任何非单词字符，如 [^[:word:]]

类缩写转义符在方括号表达式中也起作用，尽管上面显示的定义在该上下文中在语法上不太有效。例如，[a-c\d]等同于[a-c[:digit:]]。

表 9.22. 正则表达式约束转义符

转义说明
\A仅匹配字符串开头（请参阅 第 9.7.3.5 节，了解它与 ^ 有何不同）
\m仅匹配单词开头
\M仅匹配单词结尾
\y仅匹配单词开头或结尾
\Y仅匹配不是单词开头或结尾的点
\Z仅匹配字符串结尾（请参阅 第 9.7.3.5 节，了解它与 $ 有何不同）

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

表 9.23. 正则表达式反向引用

转义说明
\m（其中 m 是一个非零数字）对第 m 个子表达式的反向引用
\mnn（其中 m 是一个非零数字，nn 是更多数字，并且十进制值 mnn 不大于到目前为止看到的闭合捕获括号的数量）对第 mnn 个子表达式的反向引用

注意

八进制字符输入转义和反向引用之间存在固有的歧义，通过以下启发式方法解决，如上所述。前导零始终表示八进制转义。单个非零数字，后面不跟另一个数字，始终被视为反向引用。如果多位数字序列不是以零开头，并且它出现在合适的子表达式之后（即，该数字在反向引用的合法范围内），则该序列被视为反向引用，否则被视为八进制。

9.7.3.4. 正则表达式元语法# ​

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

RE 可以以两个特殊director前缀之一开头。如果 RE 以***:开头，则 RE 的其余部分将被视为 ARE。（这通常在PostgreSQL中没有效果，因为假定 RE 是 ARE；但是，如果通过 regex 函数的*flags*参数指定了 ERE 或 BRE 模式，则它确实会产生效果。）如果 RE 以***=开头，则 RE 的其余部分将被视为一个字符串常量，其中所有字符都被视为普通字符。

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

表 9.24. ARE 嵌入选项字母

选项说明
bRE 的其余部分是 BRE
c区分大小写的匹配（覆盖运算符类型）
eRE 的其余部分是 ERE
i不区分大小写的匹配（参见 第 9.7.3.5 节）（覆盖运算符类型）
mn 的历史同义词
n区分换行符的匹配（参见 第 9.7.3.5 节）
p部分区分换行符的匹配（参见 第 9.7.3.5 节）
qRE 的其余部分是文本 (“带引号”) 字符串，所有普通字符
s不区分换行符的匹配（默认）
t严格语法（默认；见下文）
w反向部分区分换行符 (“奇怪”) 匹配（见 第 9.7.3.5 节）
x扩展语法（见下文）

嵌入式选项在终止序列的)处生效。它们只能出现在 ARE 的开头（在***:导向符之后，如果有的话）。

除了通常的 (严格) RE 语法（其中所有字符都有意义）之外，还有一种扩展语法，可通过指定嵌入式x选项获得。在扩展语法中，RE 中的空白字符将被忽略，#和以下换行符（或 RE 的结尾）之间的所有字符也将被忽略。这允许对复杂的 RE 进行分段和注释。该基本规则有三个例外

  • \ 前面的空白字符或 # 将被保留

  • 括号表达式中的空白或 # 将被保留

  • 空白和注释不能出现在多字符符号中，例如 (?:

为此目的，空白字符是空白、制表符、换行符和属于*space*字符类的任何字符。

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

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

9.7.3.5. 正则表达式匹配规则# ​

如果 RE 可以匹配给定字符串的多个子字符串，则 RE 将匹配字符串中最先开始的那个子字符串。如果 RE 可以匹配从该点开始的多个子字符串，则将采用最长可能匹配或最短可能匹配，具体取决于 RE 是贪婪还是非贪婪。

RE 是否贪婪由以下规则确定

  • 大多数原子和所有约束没有贪婪属性（因为它们无论如何都不能匹配可变数量的文本）。

  • 在 RE 周围添加括号不会改变其贪婪性。

  • 具有固定重复量词（{m} 或 {m}?）的量化原子与原子本身具有相同的贪婪度（可能无贪婪度）。

  • 具有其他普通量词（包括 {m,n}，其中 m 等于 n）的量化原子是贪婪的（优先最长匹配）。

  • 具有非贪婪量词（包括 {m,n}?，其中 m 等于 n）的量化原子是非贪婪的（优先最短匹配）。

  • 分支（即没有顶级 | 运算符的 RE）的贪婪度与其中具有贪婪度属性的第一个量化原子相同。

  • 由 | 运算符连接的两个或更多分支组成的 RE 始终是贪婪的。

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

这意味着什么的一个示例

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

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

简而言之，当一个 RE 同时包含贪婪和非贪婪子表达式时，总匹配长度要么尽可能长，要么尽可能短，具体取决于分配给整个 RE 的属性。分配给子表达式的属性仅影响它们允许相对于彼此“吃掉”多少匹配。

量词{1,1}和{1,1}?可分别用于对子表达式或整个 RE 强制贪婪或非贪婪。当您需要整个 RE 具有与其元素推断出的不同的贪婪属性时，这很有用。例如，假设我们尝试将包含一些数字的字符串分离为数字以及它们之前和之后的各个部分。我们可能会尝试像这样操作

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

这不起作用：第一个.*是贪婪的，因此它尽可能“吃掉”，将\d+留到最后一个可能的位置，即最后一个数字。我们可能会尝试通过使其非贪婪来修复它

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

这也不起作用，因为现在整个 RE 是非贪婪的，因此它会尽快结束整体匹配。我们可以通过强制整个 RE 贪婪来获得我们想要的结果

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

将 RE 的整体贪婪与组件的贪婪分开控制，可以在处理可变长度模式时提供极大的灵活性。

在决定什么更长或更短时，匹配长度以字符为单位进行测量，而不是整理元素。空字符串被认为比完全不匹配更长。例如：bb*匹配abbbc的三个中间字符；(week|wee)(night|knights)匹配weeknights的全部十个字符；当(.*).*与abc匹配时，带括号的子表达式匹配所有三个字符；当(a*)*与bc匹配时，整个 RE 和带括号的子表达式都匹配一个空字符串。

如果指定不区分大小写的匹配，效果就像所有大小写区别都从字母表中消失了。当以普通字符形式出现在方括号表达式之外的多重大小写字母出现时，它实际上会转换成包含两种大小写的方括号表达式，例如，x变成[xX]。当它出现在方括号表达式内时，它的所有大小写对应项都会添加到方括号表达式中，例如，[x]变成[xX]，[^x]变成[^xX]。

如果指定区分换行符的匹配，.和使用^的方括号表达式将永远不会匹配换行符（因此，除非 RE 明确包含换行符，否则匹配不会跨行），并且^和$将分别匹配换行符后和换行符前的空字符串，除了分别匹配字符串的开头和结尾。但 ARE 转义符\A和\Z继续仅匹配字符串的开头或结尾only。此外，无论此模式如何，字符类简写\D和\W都将匹配换行符。（在PostgreSQL14 之前，它们在区分换行符的模式中不匹配换行符。编写[^[:digit:]]或[^[:word:]]以获得旧行为。）

如果指定部分区分换行符的匹配，这会影响.和方括号表达式，就像区分换行符的匹配一样，但不会影响^和$。

如果指定了反向部分新行敏感匹配，这会影响^和$，就像新行敏感匹配一样，但不会影响.和方括号表达式。这并没有什么用，但为了对称而提供。

9.7.3.6. 限制和兼容性# ​

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

ARE 的唯一功能与 POSIX ERE 实际不兼容的是\不会在方括号表达式内失去其特殊意义。所有其他 ARE 功能使用的语法在 POSIX ERE 中是非法的或具有未定义或未指定的效果；导演的***语法同样超出了 BRE 和 ERE 的 POSIX 语法。

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

9.7.3.7. 基本正则表达式# ​

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

9.7.3.8. 与 SQL 标准和 XQuery 的差异# ​

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

  • LIKE_REGEX

  • OCCURRENCES_REGEX

  • POSITION_REGEX

  • SUBSTRING_REGEX

  • TRANSLATE_REGEX

PostgreSQL当前未实现这些运算符和函数。您可以在每种情况下获得大致等效的功能，如表 9.25中所示。（此表中已省略两侧的各种可选子句。）

表 9.25. 正则表达式函数等效项

SQL 标准PostgreSQL
string LIKE_REGEX patternregexp_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)

许多其他 SQL 实现中也提供了与 PostgreSQL 提供的正则表达式函数类似的函数，而 SQL 标准函数的实现范围并不广。每个实现中正则表达式语法的某些细节可能有所不同。

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

  • 不支持 XQuery 字符类减法。此功能的一个示例是仅使用以下内容匹配英语辅音：[a-z-[aeiou]]。

  • 不支持 XQuery 字符类简写 \c、\C、\i 和 \I。

  • 不支持使用 \p{UnicodeProperty} 或反向 \P{UnicodeProperty} 的 XQuery 字符类元素。

  • POSIX 根据现行区域设置（可以通过将 COLLATE 子句附加到运算符或函数来控制）解释诸如 \w（请参见 表 9.21）之类的字符类。XQuery 通过引用 Unicode 字符属性来指定这些类，因此仅在区域设置遵循 Unicode 规则的情况下才能获得等效行为。

  • SQL 标准（不是 XQuery 本身）试图迎合比 POSIX 更多的 “换行符” 变体。上面描述的新行敏感匹配选项仅将 ASCII NL (\n) 视为换行符，但 SQL 希望我们也把 CR (\r)、CRLF (\r\n)（Windows 风格的换行符）和一些仅限 Unicode 的字符（如行分隔符 (U+2028)）视为换行符。值得注意的是，根据 SQL，. 和 \s 应将 \r\n 算作一个字符，而不是两个字符。

  • 在 表 9.20 中描述的字符条目转义中，XQuery 仅支持 \n、\r 和 \t。

  • XQuery 不支持方括号表达式中字符类的 [:name:] 语法。

  • XQuery 没有前瞻或后顾约束，也没有 表 9.22 中描述的任何约束转义。

  • 在 第 9.7.3.4 节 中描述的元语法形式在 XQuery 中不存在。

  • XQuery 定义的正则表达式标志字母与 POSIX 的选项字母相关，但并不相同（表 9.24）。虽然 i 和 q 选项的行为相同，但其他选项的行为不同

    • XQuery 的 s（允许点匹配换行符）和 m（允许 ^ 和 $ 在换行符处匹配）标志提供对与 POSIX 的 n、p 和 w 标志相同行为的访问，但它们 不 匹配 POSIX 的 s 和 m 标志的行为。特别注意，点匹配换行符是 POSIX 中的默认行为，但在 XQuery 中不是。

    • XQuery 的 x（忽略模式中的空白）标志与 POSIX 的扩展模式标志明显不同。POSIX 的 x 标志还允许 # 在模式中开始注释，并且 POSIX 不会忽略反斜杠后的空白字符。