PostgreSQL模式匹配的方法 LIKE等

PostgreSQL 提供了三种实现模式匹配的方法：传统 SQL 的 LIKE 操作符、SQL99 新增的 SIMILAR TO 操作符、 POSIX 风格的正则表达式。另外还有一个模式匹配函数 substring可用，它可以使用 SIMILAR TO 风格或者 POSIX 风格的正则表达式

 LIKE

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

每个 pattern 定义一个字符串的集合。如果该 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 子句指定一个。要匹配逃逸字符本身，写两个逃逸字符。

请注意反斜杠在字符串文本里已经有特殊含义了，所以如果你写一个包含反斜杠的模式常量，那你就要在 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 pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE 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(string from pattern for escape-character) 函数提供了一个从字符串中抽取一个匹配 SQL 正则表达式模式的子字符串功能。和 SIMILAR TO 一样，声明的模式必须匹配整个字符串，否则函数失效并返回 NULL 。为了标识在成功的时候应该返回的模式部分，模式必须出现后跟双引号(")的两个逃逸字符。匹配这两个标记之间的模式的字符串将被返回。

一些例子：

substring('foobar' from '%#"o_b#"%' for '#')   oob
substring('foobar' from '#"o_b#"%' for '#')    NULL

正则表达式: