PostgreSQL提供了三种独立的实现模式匹配的方法:SQL LIKE
操作符、更近一些的SIMILAR TO
操作符(SQL:1999 里添加进来的)和POSIX-风格的正则表达式。除了这些基本的“这个串匹配这个模式吗?”操作符外,还有一些函数可用于提取或替换匹配子串并在匹配位置分离一个串。
如果你的模式匹配的要求超出了这些,请考虑用 Perl 或 Tcl 写一个用户定义的函数。
虽然大部分的正则表达式搜索都能被很快地执行,但是正则表达式仍可能被 人为地弄成需要任意长的时间和任意量的内存进行处理。要当心从不怀好意 的来源接受正则表达式搜索模式。如果必须这样做,建议加上语句超时限制。
使用SIMILAR TO
模式的搜索具有同样的安全性危险,
因为SIMILAR TO
提供了很多和
POSIX-风格正则表达式相同的能力。
LIKE
搜索比其他两种选项简单得多,因此在使用
不怀好意的模式来源时要更安全些。
这三种类型的模式匹配算子都不支持非确定性拼贴。 如果需要的话,可以在表达式中应用不同的拼贴来绕过这个限制。
LIKE
string
LIKEpattern
[ESCAPEescape-character
]string
NOT LIKEpattern
[ESCAPEescape-character
]
如果该string
匹配了提供的pattern
,那么LIKE
表达式返回真(和预期的一样,如果LIKE
返回真,那么NOT LIKE
表达式返回假, 反之亦然。一个等效的表达式是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 节。
请注意反斜线在串文本里已经有特殊含义了,所以如果你写一个 包含反斜线的模式常量,那你就要在 SQL 语句里写两个反斜线。 因此,写一个匹配单个反斜线的模式实际上要在语句里写四个反斜线。 你可以通过用 ESCAPE 选择一个不同的逃逸字符 来避免这样;这样反斜线就不再是 LIKE 的特殊字符了。 但仍然是字符文本分析器的特殊字符,所以你还是需要两个反斜线。)
我们也可以通过写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
operator
的任何(subquery
)构造,尽管这里不能包含ESCAPE
子句。
在某些晦涩难懂的情况下,可能需要用底层操作符名称替代。
另请参阅以^@
开头的运算符和相应的starts_with()
函数,
在需要简单匹配字符串开头的情况下非常有用。
SIMILAR TO
正则表达式string
SIMILAR TOpattern
[ESCAPEescape-character
]string
NOT SIMILAR TOpattern
[ESCAPEescape-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正则表达式模式的子字符串。 该函数可以按照标准SQL99语法编写。
substring(string
similarpattern
escapeescape-character
)
或使用现在废除的 SQL:1999 语法:
substring(string
frompattern
forescape-character
)
或作为一个普通的三参数函数:
substring(string
,pattern
,escape-character
)
与SIMILAR TO
一样,指定的模式必须与整个数据字符串匹配,否则函数失败并返回空值。 为了表示匹配的数据子字符串的模式中,模式中应该包含两个转义字符的出现,并在后面加上一个双引号("
)。 匹配成功后,将返回与这些分隔符之间的模式部分匹配的文本。
转义-双引号分隔符实际上是 将子字符串
的模式分成三个独立的 正则表达式;例如,竖条(|
) 三节中的任何一节只影响到该节。 此外,第一节和第三种正则表达式的定义是为了匹配最小的 尽可能多的文字,而不是最大的文字,当有歧义的时候,就不应该是最大的文字。关于有多少数据字符串符合哪种模式。 (在POSIX术语中,第一和第三种正则表达式被强行规定为非贪婪)。
作为对SQL标准的扩展,PostgreSQL只允许有一个转义双引号分隔符,在这种情况下,第三个正则表达式被视为空;或者没有分隔符,在这种情况下,第一个和第三个正则表达式被视为空。
一些例子,使用#"
定界返回串:
substring('foobar' similar '%#"o_b#"%' escape '#') oob substring('foobar' similar '#"o_b#"%' escape '#') NULL
表 9.16列出了所有可用于 POSIX 正则表达式模式匹配的操作符。
表 9.16. 正则表达式匹配操作符
操作符 描述 例子 |
---|
字符串匹配正则表达式,大小写敏感
|
字符串匹配正则表达式,大小写不敏感
|
字符串不匹配正则表达式,大小写敏感
|
字符串不匹配正则表达式,大小写不敏感
|
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(
,提供了抽取一个匹配 POSIX 正则表达式模式的子串的方法。
如果没有匹配它返回空值,否则就是匹配模式的文本中的第一部分。
但是如果该模式包含任何圆括号,那么将返回匹配第一对子表达式(对应第一个左圆括号的) 的文本。
如果你想在表达式里使用圆括号而又不想导致这个例外,那么你可以在整个表达式外边放上一对圆括号。
如果你需要在想抽取的子表达式前有圆括号,参阅后文描述的非捕获性圆括号。
string
from pattern
)
一些例子:
substring('foobar' from 'o.b') oob substring('foobar' from 'o(.)b') o
regexp_count
函数计算 POSIX 正则表达式模式与字符串匹配的次数。
它的语法为
regexp_count
(string
,
pattern
[, start
[, flags
]])。
pattern
在string
中进行搜索,
通常从字符串的开头开始,但如果提供了start
参数,则从该字符索引开始。
flags
参数是一个可选的文本字符串,包含零个或多个单字母标志,改变函数的行为。
例如,在flags
中包含i
指定不区分大小写匹配。
支持的标志在表 9.24中描述。
一些示例:
regexp_count('ABCABCAXYaxy', 'A.') 3 regexp_count('ABCABCAXYaxy', 'A.', 1, 'i') 4
regexp_instr
函数返回字符串中第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
的元素是匹配
pattern
的第n
个括号子表达式的子字符串(不包括“非捕获”括号;详情见下文)。
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()
。
然而,在PostgreSQL版本15及以上版本中才存在
regexp_substr()
。在旧版本中工作时,您可以提取
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
接受在表 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()
仅存在于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
]).
(注意,除非指定了start
,否则无法指定N
,
但flags
可以在任何情况下给出。)
如果pattern
没有匹配到source
字符串,
则返回未更改的source
字符串。如果有匹配,则返回带有
replacement
字符串替换匹配子字符串的source
字符串。
replacement
字符串可以包含\
n
,
其中n
为1至9,表示应插入与模式的第n
个括号子表达式匹配的源子字符串,
也可以包含\&
表示应插入与整个模式匹配的子字符串。
如果需要在替换文本中放置一个字面上的反斜杠,则写\\
。
在string
中搜索pattern
,
通常从字符串的开头开始,但如果提供了start
参数,则从该字符索引开始。
默认情况下,只替换模式的第一个匹配项。
如果指定了N
且大于零,则替换模式的第N
个匹配项。
如果给出了g
标志,或者指定了N
且为零,则在或之后的所有匹配项
位置都将被替换。 (当指定了N
时,g
标志将被忽略。)
flags
参数是一个可选的文本字符串,包含零个或多个单字母标志,用于更改函数的行为。
支持的标志(尽管不包括g
)在表 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 行) 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 行) 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 行)
正如最后一个示例所示,regexp分割函数会忽略出现在字符串开头或结尾 或紧跟在前一个匹配项之后的零长度匹配。这与其他regexp函数实现的 严格的regexp匹配定义相矛盾,但在实践中通常是最方便的行为。 其他软件系统如Perl使用类似的定义。
regexp_substr
函数返回与POSIX正则表达式模式匹配的子字符串,
如果没有匹配,则返回NULL
。它的语法为
regexp_substr
(string
,
pattern
[, start
[, N
[, flags
[, subexpr
]]]])。
pattern
在string
中进行搜索,
通常从字符串的开头开始,但如果提供了start
参数,则从该字符索引开始。
如果指定了N
,则返回模式的第N
次匹配,
否则返回第一次匹配。参数flags
是一个可选文本字符串,
包含零个或多个单字母标志,可以改变函数的行为。支持的标志在表 9.24中描述。
对于包含括号子表达式的模式,subexpr
是一个整数,
表示感兴趣的子表达式:结果是与该子表达式匹配的子字符串。
子表达式按其前导括号的顺序编号。
当省略或为零时,subexpr
的结果是整个匹配,而不考虑括号子表达式。
一些示例:
regexp_substr('number of your street, town zip, FR', '[^,]+', 1, 2) 城镇 邮编 regexp_substr('ABCDEFGHI', '(c..)(...)', 1, 1, 'i', 2) FGH
PostgreSQL的正则表达式是使用 Henry Spencer 写的一个包来实现的。下面的正则表达式的大部分描述都是从他的手册页中逐字拷贝过来的。
正则表达式(RE),在POSIX 1003.2 中定义, 它有两种形式:扩展的RE或者是ERE(大概地说就是那些在egrep
里的), 基本的RE或者是BRE(大概地说就是那些在ed
里的)。PostgreSQL支持两种形式,并且还实现了一些POSIX标准中没有但是在类似 Perl 或者 Tcl 这样的语言中得到广泛应用的一些扩展。使用了那些非POSIX扩展的RE叫高级RE, 或者本文档里说的ARE。ARE 几乎完全是 ERE 的超集,但是 BRE 有几个符号上的不兼容(以及更多的限制)。我们首先描述 ARE 和 ERE 形式, 描述那些只适用于 ARE 的特性,然后描述 BRE 的区别是什么。
PostgreSQL初始时总是推测一个正则表达式遵循 ARE 规则。但是,可以通过为 RE 模式预置一个embedded option来选择限制更多的 ERE 或 BRE 规则,如第 9.7.3.4 节中所述。这对为期望准确的POSIX 1003.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 ) |
{ | 如果后面跟着一个字符,而不是数字, 那么就匹配左花括弧{ ;如果跟着一个数字, 那么它是range 的开始(见下文) |
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 的子串开始的任何点的positive lookahead匹配(只对 ARE) |
(?! re ) | 在匹配re 的子串开始的任何点的negative lookahead匹配(只对 ARE) |
(?<= re ) | 只要有一个点上有一个子串匹配re 端, positive lookbehind就在这个点上匹配(只对 ARE) |
(?<! re ) | 只要有一个点上没有子串匹配re 端, negative lookbehind就在这个点上匹配(只对 ARE) |
Lookahead 和 lookbehind 约束不能包含后引用 (参阅第 9.7.3.3 节),并且其中的所有圆括号 都被认为是非捕获的。
方括号表达式是一个包围在[]
中的字符列表。它通常匹配列表中的任意单个字符(但见下文)。 如果列表以^
开头,它匹配任意单个不在该列表参与部分中的字符。如果该列表中两个字符用-
隔开, 那它就是那两个字符(包括在内)之间的所有字符范围的缩写,例如,在ASCII中[0-9]
匹配任何十进制数字。两个范围共享一个端点是非法的,例如,a-c-e
。范围与字符集关系密切, 可移植的程序应该避免依靠它们。
想在列表中包含文本]
,可以让它做列表的首字符(如果使用了^
,需要放在其后)。 想在列表中包含文本-
,可以让它做列表的首字符或者尾字符,或者一个范围的第二个端点。 想在列表中把文本-
当做范围的起点, 把它用[.
和.]
包围起来,这样它就成为一个排序元素(见下文)。 除了这些字符本身、一些用[
的组合(见下段)以及逃逸(只在 ARE 中有效)以外,所有其它特殊字符 在方括号表达式里都失去它们的特殊含义。特别是,在 ERE 和 BRE 规则下\
不是特殊的, 但在 ARE 里,它是特殊的(引入一个逃逸)。
在一个方括号表达式里,一个排序元素(一个字符、一个被当做一个单一字符排序的多字符序列或者一个表示上面两种情况的排序序列名称) 包含在[.
和.]
里面的时候表示该排序元素的字符序列。该序列被当做该方括号列表 的一个单一元素。这允许一个包含多字符排序元素的方括号表达式去匹配多于一个字符,例如,如果排序序列包含一个ch
排序元素, 那么 RE [[.ch.]]*c
匹配chchcc
的头五个字符。
PostgreSQL当前不支持多字符排序元素。这些信息描述了将来可能有的行为。
在方括号表达式里,包围在[=
和=]
里的排序元素是一个等价类, 代表等效于那一个的所有排序元素的字符序列,包括它本身(如果没有其它等效排序元素,那么就好象封装定界符是[.
和 .]
)。例如,如果o
和^
是一个等价类的成员,那么[[=o=]]
、[[=^=]]
和[o^]
都是同义的。一个等价类不能是一个范围的端点。
在方括号表达式里,在[:
和:]
里面封装的字符类的名字代表属于该类的所有字符的列表。
字符类不能作为范围的端点使用。
POSIX标准定义了这些字符类的名称:
alnum
(字符和数字),
alpha
(字符),
blank
(空格和制表符tab),
cntrl
(控制符),
digit
(数位数),
graph
(空格除外可打印字符),
lower
(小写字母),
print
(包含空格可打印字符),
punct
(标点符号),
space
(空白),
upper
(大写字母),
和 xdigit
(十六进制数).
对于7位ASCII字符集中的字符来说,这些标准字符类的行为在不同平台上一般是一致的。
一个给定的非ASCII字符是否被认为属于这些类别中的一个,取决于正则表达式函数或运算符使用的collation(见第 24.2 节),或者默认情况下取决于数据库的LC_CTYPE
locale设置(见第 24.1 节)。
非ASCII字符的分类在不同的平台上会有不同的分类,即使是在类似命名的locale中也是如此。
(但C
locale从不认为任何非ASCII字符属于上述任何一类)。
除了这些标准字符类之外,PostgreSQL定义了word
字符类,等同于alnum
加上下划线(_
), 以及ascii
字符类,它完全包含7位ASCII字符集。
方括号表达式里有两个特例:方括号表达式[[:<:]]
和[[:>:]]
是约束,分别匹配一个单词开头和结束的空串。
单词定义为一个单词字符序列,前面和后面都没有其它单词字符。
单词字符是属于word
字符类的任何字符,也就是,任何字母、数字或下划线。
这是一个扩展,兼容POSIX 1003.2, 但那里面并没有说明, 而且在准备移植到其他系统里去的软件里一定要小心使用。
通常下文描述的约束逃逸更好些(它们并非更标准,但是更容易键入)。
逃逸是以\
开头,后面跟着一个字母数字字符得特殊序列。 逃逸有好几种变体:字符项、类缩写、约束逃逸以及后引用。在 ARE 里, 如果一个\
后面跟着一个字母数字,但是并未组成一个合法的逃逸, 那么它是非法的。在 ERE 中没有逃逸:在方括号表达式之外,一个后面跟着字母数字字符的\
只是表示该字符是一个普通的字符,而且在一个方括号表达式里,\
是一个普通的字符(后者实际上在 ERE 和 ARE 不兼容)。
字符项逃逸用于便于我们在 RE 中声明那些不可打印的或其他习惯的字符。它们显示在表 9.20中。
类缩写逃逸用来提供一些常用的字符类缩写。它们显示在表 9.21中。
约束逃逸是一个约束,如果满足特定的条件,它匹配该空串。它们显示在表 9.22中。
后引用(\
n
)匹配数字\
n
指定的被前面的圆括号子表达式匹配的同一个串 (参阅表 9.23)。
例如, ([bc])\1
匹配bb
或者cc
, 但是不匹配bc
或者cb
。
RE 中子表达式必须完全在后引用前面。子表达式以它们的先导圆括号的顺序编号。
非捕获圆括号并不定义子表达式。
反向引用仅考虑被引用的子表达式匹配的字符串字符,而不考虑其中包含的任何约束。
例如,(^\d)\1
将匹配 22
。
表 9.20. 正则表达式字符项逃逸
逃逸 | 描述 |
---|---|
\a | 警告(响铃)字符,和 C 中一样 |
\b | 退格,和 C 中一样 |
\B | 反斜线(\ )的同义词,用来减少双写反斜线 |
\c X | (其中X 是任意字符)低序5位和X 相同的字符,它的其他位都是零 |
\e | 排序序列名为ESC 的字符,如果无法做到该字符为八进制值 033 |
\f | 换页,和 C 中一样 |
\n | 新行,和 C 中一样 |
\r | 回车,和 C 中一样 |
\t | 水平制表符,和 C 中一样 |
\u wxyz | (其中wxyz 正好是四个十六进制位)十六进制值为0x wxyz 的字符 |
\U stuvwxyz | (其中stuvwxyz 正好是八个十六进制位)十六进制值为0x stuvwxyz 的字符
|
\v | 垂直制表符,和 C 中一样 |
\x hhh | (其中hhh 是十六进制位的任意序列)十六进制值为0x hhh 的字符(一个单一字符,不管用了多少个十六进制位)
|
\0 | 值为0 (空字节)的字符 |
\ xy | (其中xy 正好是两个八进制位,并且不是一个后引用)八进制值为0 xy 的字符 |
\ xyz | (其中xyz 正好是三个八进制位,并且不是一个后引用)八进制值为0 xyz 的字符 |
十六进制位是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 个子表达式的后引用 |
在八进制字符项逃逸和后引用之间有一个历史继承的歧义存在,这个歧义是 通过下面的启发式规则解决的,像上面描述地那样。前导零总是表示这是一个八进制逃逸。 而单个非零数字,如果没有跟着任何其它位,那么总是被认为后引用。 一个多位的非零开头的序列也被认为是后引用,只要它出现在合适的子表达式后面 (也就是说,在后引用的合法范围中的数),否则就被认为是一个八进制。
除了上面描述的主要语法之外,还有几种特殊形式和杂项语法。
如果一个 RE 以***:
开头,那么剩下的 RE 都被当作 ARE(这在PostgreSQL中通常是无效的,因为 RE 被假定为 ARE,但是如果 ERE 或 BRE 模式通过flags
参数被指定为一个正则表达式函数时,它确实能产生效果)。如果一个 RE 以***=
开头, 那么剩下的 RE 被当作一个文本串,所有的字符都被认为是一个普通字符。
一个 ARE 可以以嵌入选项开头:一个序列(?
xyz
)
(这里的xyz
是一个或多个字母字符)声明影响剩余 RE 的选项。 这些选项覆盖任何前面判断的选项 — 特别地,它们可以覆盖一个正则表达式操作符隐含的大小写敏感的行为,或者覆盖flags
参数中的正则表达式函数。可用的选项字母在表 9.24中显示。注意这些同样的选项字母也被用在正则表达式函数的flags
参数中。
表 9.24. ARE 嵌入选项字母
选项 | 描述 |
---|---|
b | RE的剩余部分是一个BRE |
c | 大小写敏感的匹配(覆盖操作符类型) |
e | RE的剩余部分是一个ERE |
i | 大小写不敏感的匹配(见第 9.7.3.5 节)(覆盖操作符类型) |
m | n 的历史原因的同义词 |
n | 新行敏感的匹配(见第 9.7.3.5 节) |
p | 部分新行敏感的匹配(见第 9.7.3.5 节) |
q | RE的剩余部分是一个文字(“quoted”)串,全部是普通字符 |
s | 非新行敏感的匹配(默认) |
t | 紧语法(默认,见下文) |
w | 逆部分新行敏感(“怪异”)的匹配(见第 9.7.3.5 节) |
x | 扩展语法(见下文) |
嵌入选项在)
终止序列时发生作用。它们只在 ARE 的开始处起作用 (在任何可能存在的***:
控制器后面)。
除了通常的(紧)RE 语法(这种情况下所有字符都有效), 还有一种扩展语法,可以通过声明嵌入的x
选项获得。在扩展语法里,RE 中的空白字符被忽略,就像那些在#
和其后的新行(或 RE 的末尾)之间的字符一样。这样就允许我们给一个复杂的 RE 分段和注释。不过这个基本规则有三种例外:
空白字符或前置了\
的#
将被保留
方括号表达式里的空白或者#
将被保留
在多字符符号里面不能出现空白和注释,例如(?:
为了这个目的,空白是空格、制表符、新行和任何属于空白
字符类的字符。
最后,在 ARE 里,方括号表达式外面,序列(?#
ttt
)
(其中ttt
是任意不包含一个)
)的文本)是一个注释, 它被完全忽略。同样,这样的东西是不允许出现在多字符符号的字符中间的,例如 (?:
。这种注释更像是一种历史产物而不是一种有用的设施,并且它们的使用已经被废弃;请使用扩展语法来替代。
如果声明了一个初始的***=
控制器,那么所有这些元语法扩展都不能使用,因为这样表示把用户输入当作一个文字串而不是 RE 对待。
在 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})'); 结果:123
SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); 结果:1
在第一个例子里,RE 作为整体是贪婪的,因为Y*
是贪婪的。它可以匹配从Y
开始的东西,并且它匹配从这个位置开始的最长的串, 也就是,Y123
。输出是这里的圆括号包围的部分,或者说是123
。在第二个例子里, RE 总体上是一个非贪婪的 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 的组件的贪婪性之外控制 RE 的整体贪婪性为处理变长模式提供了 很大的灵活性。
在决定更长或者更短的匹配时,匹配长度是以字符衡量的,而不是排序元素。一个空串会被认为比什么都不匹配长。例如:bb*
匹配abbbc
的中间三个字符;(week|wee)(night|knights)
匹配weeknights
的所有十个字符; 而(.*).*
匹配 abc
的时候,圆括号包围的子表达式匹配所有三个字符;当(a*)*
被拿来匹配bc
时,整个 RE 和圆括号 子表达式都匹配一个空串。
如果声明了大小写无关的匹配,那么效果就好像所有大小写区别在字母表中消失了。如果在多个情况中一个字母以一个普通字符的形式出现在方括号表达式外面,那么它实际上被转换成 一个包含大小写的方括号表达式,也就是说,x 变成 [xX]。 如果它出现在一个方括号表达式里面,那么它的所有大小写的同族都被加入 方括号表达式中,也就是说,x
变成[xX]
。当它出现在一个方括号表达式内时,它的所有大小写副本都被加入到方括号表达式中,例如, [x]
会变成[xX]
,而[^x]
会变成[^xX]
。
如果指定了换行敏感的匹配,.
和使用^
的方括号表达式 将永远不会匹配换行字符(这样,匹配就不会跨越行,除非 RE 显式地包含了换行)并且^
和$
除了分别匹配串开头和结尾之外,还将分别匹配换行后面和前面的空串。
但是 ARE 逃逸\A
和\Z
仍然只匹配串的开头和结尾。
此外,字符类缩写符\D
和\W
将匹配换行,而无论这种模式如何。
(在PostgreSQL 14之前,它们在换行敏感模式下不匹配换行。
写入[^[:digit:]]
或 [^[:word:]]
以获取旧的行为。)
如果指定了部分新行敏感的匹配,那么它影响.
和方括号表达式, 这个时候和新行敏感的匹配一样,但是不影响^
和$
。
如果指定了逆新行敏感匹配,那么它影响^
和$
,其作用和在新行敏感的匹配里一样,但是不影响.
和方括号表达式。这个并不是很有用,只是为了满足对称性而提供的。
在这个实现里,对 RE 的长度没有特别的限制。但是,那些希望高移植性的程序应该避免使用长度超过 256 字节的 RE,因为 POSIX 兼容 的实现可以拒绝接受这样的 RE。
ARE 实际上和 POSIX ERE 不兼容的唯一的特性是在方括号表达式里\
并不失去它特殊的含义。所有其它 ARE 特性都使用在 POSIX ERE 里面是非法或者是未定义、未声明效果的语法;指示器的***
就是在 POSIX 的 BRE 和 ERE 之外的语法。
许多 ARE 扩展都是从 Perl 那里借来的(但是有些被做了修改来清理它们),以及一些 Perl 里没有出现的扩展。要注意的不兼容性包括\b
、\B
、对结尾的新行缺乏特别的处理、对那些被新行敏感匹配的东西附加的补齐方括号表达式、在 lookahead/lookbehind 约束里对圆括号和后引用的限制以及最长/最短 匹配(而不是第一匹配)的语义。
BREs 在几个方面和 ERE 不太一样。在 BRE 中,|
、+
和?
都是普通字符并且没有与它们功能等价的东西。范围的定界符是\{
和\}
, 因为 {
和}
本身是普通字符。嵌套的子表达式的圆括号是\(
和\)
,因为(
和)
自身是普通字符。除非在 RE 开头或者是圆括号子表达式开头,^
都是一个普通字符。 除非在 RE 结尾或者是圆括号子表达式的结尾,$
是一个普通字符。如果*
出现在 RE 开头或者是圆括号封装的子表达式开头 (前面可能有^
),那么它是个普通字符。最后,可以用单数字的后引用,\<
和\>
分别是[[:<:]]
和[[:>:]]
的同义词;在 BRE 中没有其它可用的逃逸。
自SQL:2008以来,SQL标准包括正则表达式运算符和函数,根据XQuery正则表达式标准进行模式匹配:
LIKE_REGEX
OCCURRENCES_REGEX
POSITION_REGEX
SUBSTRING_REGEX
TRANSLATE_REGEX
PostgreSQL目前尚未实现这些运算符和函数。您可以在每种情况下获得近似等效的功能,如表 9.25中所示。(此表中已省略了双方的各种可选子句。)
表 9.25. 正则表达式函数等效性
SQL标准 | PostgreSQL |
---|---|
| regexp_like( 或
|
OCCURRENCES_REGEX( | regexp_count( |
POSITION_REGEX( | regexp_instr( |
SUBSTRING_REGEX( | regexp_substr( |
TRANSLATE_REGEX( | regexp_replace( |
与PostgreSQL提供的类似的正则表达式函数在许多其他SQL实现中也可用,而SQL标准函数并不如此广泛实现。 每个实现中正则表达式语法的一些细节可能会有所不同。
SQL标准运算符和函数使用XQuery正则表达式,这些正则表达式与上述ARE语法非常接近。现有基于POSIX的正则表达式功能和XQuery正则表达式之间的显着差异包括:
XQuery字符类减法不受支持。一个示例是使用以下内容仅匹配英语辅音:[a-z-[aeiou]]
。
XQuery字符类简写\c
、\C
、\i
和\I
不受支持。
使用\p{UnicodeProperty}
或其反义\P{UnicodeProperty}
来定义XQuery字符类元素是不被支持的。
POSIX解释字符类,如\w
(参见表 9.21)
根据当前区域设置(您可以通过将COLLATE
子句附加到运算符或函数来控制)。
XQuery通过参考Unicode字符属性来指定这些类别,因此只有遵循Unicode规则的区域设置才能获得等效行为。
SQL标准(而不是XQuery本身)试图适应比POSIX更多的“换行符”变体。上面描述的换行符敏感匹配选项只考虑ASCII NL(\n
)为换行符,但SQL要求我们将CR(\r
)、CRLF(\r\n
)(Windows风格的换行符)以及一些仅限于Unicode的字符,如LINE SEPARATOR(U+2028),也视为换行符。
值得注意的是,.
和\s
应该根据SQL的规定将\r\n
视为一个字符而不是两个字符。
在描述的字符输入转义中
表 9.20中,
XQuery仅支持\n
,\r
,
和\t
。
XQuery不支持方括号表达式中的字符类[:
语法。
name
:]
XQuery没有前瞻或后顾约束,也没有在表 9.22中描述的任何约束转义。
在XQuery中不存在第 9.7.3.4 节中描述的元语法形式。
XQuery定义的正则表达式标志字母与POSIX的选项字母相关,但并不相同
(表 9.24)。虽然
i
和q
选项的行为相同,其他选项则不同:
XQuery的s
(允许点匹配换行符)
和m
(允许^
和$
匹配换行符)标志提供
与POSIX的n
、p
和w
标志相同的行为,但它们
不匹配POSIX的s
和m
标志的行为。
特别注意,点匹配换行符是POSIX的默认行为,但不是XQuery的。
XQuery的x
(忽略模式中的空白)标志
与POSIX的扩展模式标志明显不同。
POSIX的x
标志还
允许#
在模式中开始一个注释,
并且POSIX不会忽略反斜杠后的空白字符。