Postgresql 提供了三种实现模式匹配的方法:传统 sql 的LIKE
操作符、sql99 新增的SIMILAR TO
操作符、 POSIX 风格的正则表达式。另外还有一个模式匹配函数substring
可用,它可以使用SIMILAR TO
风格或者 POSIX 风格的正则表达式
LIKE
string LIKE pattern [ESCAPE escape-character] string NOT LIKE escape-character]
每个pattern定义一个字符串的集合。如果该string包含在pattern代表的字符串集合里,那么LIKE
表达式返回真。和我们想像的一样,如果LIKE
返回真,那么NOT LIKE
表达式将返回假,反之亦然。一个等效的表达式是NOT (stringLIKEpattern)。
如果pattern不包含百分号或者下划线,那么该模式只代表它本身;这时候LIKE
的行为就像等号操作符。在pattern里的下划线(_)匹配任何单个字符;而一个百分号(%)匹配零或多个任何字符。
一些例子:
'abc' LIKE 'abc' true 'abc' LIKE 'a%' true 'abc' LIKE '_b_' true 'abc' LIKE 'c' false
LIKE
模式匹配总是覆盖整个字符串。要匹配在字符串内部任何位置的序列,该模式必须以百分号开头和结尾。
要匹配下划线或者百分号本身,在pattern里相应的字符必须前导逃逸字符。缺省的逃逸字符是反斜杠,但是你可以用ESCAPE子句指定一个。要匹配逃逸字符本身,写两个逃逸字符。
请注意反斜杠在字符串文本里已经有特殊含义了,所以如果你写一个包含反斜杠的模式常量,那你就要在 sql 语句里写两个反斜杠。因此,写一个匹配单个反斜杠的模式实际上要在语句里写四个反斜杠。你可以通过用ESCAPE选择一个不同的逃逸字符来避免这样;这样反斜杠就不再是LIKE
的特殊字符了。但仍然是字符文本分析器的特殊字符,所以你还是需要两个反斜杠。
我们也可以通过写成ESCAPE ''的方式关闭逃逸机制,这时,我们就不能关闭下划线和百分号的特殊含义。
关键字ILIKE可以用于替换LIKE,令该匹配就当前的区域设置是大小写无关的。这个特性不是 sql 标准,是 Postgresql 扩展。
操作符~~等效于LIKE
,而~~*等效于ILIKE
。还有!~~和!~~*操作符分别代表NOT LIKE
和NOT ILIKE
。所有这些操作符都是 Postgresql 特有的.
例子: 查找数据库中表的名字(查找以tbl_开头的表的名字)
select tablename from pg_tables where tablename like 'tbl!_%'ESCAPE '!';
利用escape转义: 上面的sql语句中就是将!作为转义字符,作用是将"_"转换成普通的"_"
SIMILAR TO正则表达式
string SIMILAR TO string NOT SIMILAR TO escape-character]
SIMILAR TO
根据自己的模式是否匹配给定字符串而返回真或者假。它和LIKE
非常类似,只不过它使用 sql 标准定义的正则表达式理解模式。sql 标准的正则表达式是在LIKE
表示法和普通的正则表达式表示法之间古怪的交叉。
类似LIKE
,SIMILAR TO
操作符只有在它的模式匹配整个字符串的时候才能成功;这一点和普通的正则表达式的习惯不同,在普通的正则表达式里,模式匹配字符串的任意部分。和LIKE
类似的地方还有SIMILAR TO
使用_和%分别匹配单个字符和任意字符串(这些和 POSIX 正则表达式里的.和.*兼容)。
除了这些从LIKE
借用的功能之外,SIMILAR TO
支持下面这些从 POSIX 正则表达式借用的模式匹配元字符:
-
|表示选择(两个候选之一)
-
*表示重复前面的项零次或更多次
-
+表示重复前面的项一次或更多次
-
()把项组合成一个逻辑项
-
[...]声明一个字符类
请注意没有提供范围重复(?和{...}),尽管它们在 POSIX 里有。同时,点(.)不是元字符。
和LIKE
一样,反斜杠关闭所有这些元字符的特殊含义;当然我们也可以用ESCAPE声明另外一个逃逸字符。
一些例子:
'abc' SIMILAR TO 'abc' true 'abc' SIMILAR TO 'a' false 'abc' SIMILAR TO '%(b|d)%' true 'abc' SIMILAR TO '(b|c)%' false
带三个参数的substring(stringfrompatternforescape-character)
函数提供了一个从字符串中抽取一个匹配 sql 正则表达式模式的子字符串功能。和SIMILAR TO一样,声明的模式必须匹配整个字符串,否则函数失效并返回 NULL 。为了标识在成功的时候应该返回的模式部分,模式必须出现后跟双引号(")的两个逃逸字符。匹配这两个标记之间的模式的字符串将被返回。
一些例子:
substring('foobar' from '%#"o_b#"%' for '#') oob substring('foobar' from '#"o_b#"%' for '#') NULL
正则表达式: