一、概述

  PostgreSQL 提供了三种独立的实现模式匹配的方法SQL LIKE 操作符、更近一些的 SIMILAR TO 操作符SQL:1999 里添加进来的和 POSIX 正则表达式。虽然大部分的正则表达式搜索都能被很快地执行但是正则表达式仍可能被人为地设置成需要任意长的时间和任意量的内存进行处理如果必须这样做建议加上语句超时限制。使用 SIMILAR TO 模式的搜索具有同样的安全性危险LIKE 搜索比其他两种选项简单得多要更安全些。

二、SQL LIKE 操作符

2.1 语法

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

参数说明

string待匹配的字符串。
pattern匹配规则。若不包含百分号或者下划线那么只匹配串本身这时 LIKE 的行为等价于等号操作符下划线_代表匹配任何单个字符百分号%匹配任何零或更多个字符。
ESCAPE自定义转义字符默认为反斜线\。

补充
PostgreSQL 还提供了标准 SQ L中没有的 ILIKE 操作符用于忽略大小写的模式匹配。

~~等效于 LIKE。
~~*等效于 ILIKE。
!~~等效于 NOT LIKE。
!~~*操作符 NOT ILIKE。

2.2 示例

--正常字符匹配
postgres=# select * from t;
 id |   name
----+----------
  1 | XIAOH
  2 | XIAOO
  3 | xiaoguo
  4 | xiaowan
  5 | xiaopiao
  6 | xiaopen
(6 rows)

postgres=# select * from t where name like 'xiao%';
 id |   name
----+----------
  3 | xiaoguo
  4 | xiaowan
  5 | xiaopiao
  6 | xiaopen
(4 rows)

postgres=# select * from t where name ~~ 'xiao%';
 id |   name
----+----------
  3 | xiaoguo
  4 | xiaowan
  5 | xiaopiao
  6 | xiaopen
(4 rows)

postgres=# select * from t where name not like 'xiao%';
 id | name
----+-------
  1 | XIAOH
  2 | XIAOO
(2 rows)

postgres=# select * from t where name !~~ 'xiao%';
 id | name
----+-------
  1 | XIAOH
  2 | XIAOO
(2 rows)

postgres=# select * from t where name ilike 'xiao%';
 id |   name
----+----------
  1 | XIAOH
  2 | XIAOO
  3 | xiaoguo
  4 | xiaowan
  5 | xiaopiao
  6 | xiaopen
(6 rows)

postgres=# select * from t where name ~~* 'xiao%';
 id |   name
----+----------
  1 | XIAOH
  2 | XIAOO
  3 | xiaoguo
  4 | xiaowan
  5 | xiaopiao
  6 | xiaopen
(6 rows)

postgres=# select * from t where name not ilike 'xiao%';
 id | name
----+------
(0 rows)

postgres=# select * from t where name !~~* 'xiao%';
 id | name
----+------
(0 rows)

--加转义匹配
postgres=# select * from t;
 id | name
----+------
  1 | \
  2 | \n
  3 | \\
  4 | a\a
(4 rows)

postgres=# select * from t where name like '\%';
 id | name
----+------
(0 rows)

postgres=# select * from t where name like '\\%';
 id | name
----+------
  1 | \
  2 | \n
  3 | \\
(3 rows)

postgres=# select * from t where name like '3\%' escape '3';
 id | name
----+------
  1 | \
  2 | \n
  3 | \\
(3 rows)

postgres=# select * from t where name like '#\%' escape '#';
 id | name
----+------
  1 | \
  2 | \n
  3 | \\
(3 rows)

postgres=# select * from t where name like '@\%' escape '@';
 id | name
----+------
  1 | \
  2 | \n
  3 | \\
(3 rows)

三、SIMILAR TO 操作符

3.1 语法

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

参数说明

string待匹配的字符串。
pattern匹配规则。若不包含百分号或者下划线那么只匹配串本身这时 LIKE 的行为等价于等号操作符下划线_代表匹配任何单个字符百分号%匹配任何零或更多个字符。
ESCAPE自定义转义字符默认为反斜线\。

除了支持上述 LIKE 的语法外SIMILAR TO 还支持 POSIX 正则表达式匹配元字符

|表示选择两个候选之一。
*表示重复前面的项零次或更多次。
+表示重复前面的项一次或更多次。
?表示重复前面的项零次或一次。
{m}表示重复前面的项刚好m次。
{m,}表示重复前面的项m次或更多次。
{m,n}表示重复前面的项至少m次并且不超过n次。

可以使用圆括号()把多个项组合成一个逻辑项。一个方括号表达式[…]声明一个字符类就像 POSIX 正则表达式一样。

3.2 示例

postgres=# select * from t;
 id |           name
----+--------------------------
  1 | ab
  2 | cdcdcdcdcdcdcdcdcdcdcdcd
  3 | efghefghefghefgh
(3 rows)

postgres=# select * from t where name similar to 'c%';
 id |           name
----+--------------------------
  2 | cdcdcdcdcdcdcdcdcdcdcdcd
(1 row)

postgres=# select * from t where name similar to '(a|c)%';
 id |           name
----+--------------------------
  1 | ab
  2 | cdcdcdcdcdcdcdcdcdcdcdcd
(2 rows)

postgres=# select * from t where name similar to '(cd)*';
 id |           name
----+--------------------------
  2 | cdcdcdcdcdcdcdcdcdcdcdcd
(1 row)

postgres=# select * from t where name similar to '(cd)+';
 id |           name
----+--------------------------
  2 | cdcdcdcdcdcdcdcdcdcdcdcd
(1 row)

postgres=# select * from t where name similar to '(ab)?';
 id | name
----+------
  1 | ab
(1 row)

postgres=# select * from t where name similar to '(efghefgh){2}';
 id |       name
----+------------------
  3 | efghefghefghefgh
(1 row)

postgres=# select * from t where name similar to '(efgh){2,}';
 id |       name
----+------------------
  3 | efghefghefghefgh
(1 row)

四、POSIX 正则表达式

4.1 匹配操作符

操作符	描述	例子
text ~ text → boolean	字符串匹配正则表达式大小写敏感	‘thomas’ ~ ‘.*thom.*’ → t
text ~* text → boolean	字符串匹配正则表达式大小写不敏感	‘thomas’ ~* ‘.*Thom.*’ → t
text !~ text → boolean	字符串不匹配正则表达式大小写敏感	‘thomas’ !~ ‘.*thomas.*’ → f
text !~* text → boolean	字符串不匹配正则表达式大小写不敏感	‘thomas’ !~* ‘.*Thom.*’ → f

4.2 常用匹配规则

匹配规则符	描述
|	选择两个候选之一
*	重复前面的项零次或更多次
+	重复前面的项一次或更多次
()	括号中内容一个逻辑项
?	前面的项零次或一次
*?	*的非贪婪模式
+?	+的非贪婪模式
??	?的非贪婪模式
{m}	重复前面的项刚好m次
{m,}	重复前面的项m次或更多次
{m,n}	重复前面的项至少m次并且不超过n次
{m}?	{m}的非贪婪模式
{m,}?	{m,}的非贪婪模式
{m,n}	{m,n}的非贪婪模式
^	匹配字串的开头
$	匹配字串的结尾
\n	匹配换行
\r	匹配回车
\t	匹配制表符
\d	匹配任意数字
\s	匹配空格
\w	匹配任意字母
\D	匹配非数字
\S	匹配非空格
\W	匹配非字母
[a-z]	匹配任意小写字母
[A-Z]	匹配任意大写字母
[^a-z]	匹配非小写字母
[^A-Z]	匹配非大写字母
[0-9]	匹配任意数字
[^0-9]	匹配非数字
[abc]	匹配a或b或c或ab或ac或bc
[a]+	匹配一个a或多个a

4.2 常用相关函数

--substring 函数匹配字符串
postgres=# select substring('foobar',3);
 substring
-----------
 obar
(1 row)

postgres=# select substring('foobar',3,2);
 substring
-----------
 ob
(1 row)

postgres=# select substring('foobar' from '..$');
 substring
-----------
 ar

--regexp_replace 函数替换字符串
postgres=# select regexp_replace('foobar','o','x');
 regexp_replace
----------------
 fxobar
(1 row)

postgres=# select regexp_replace('foobar','o','x','g');
 regexp_replace
----------------
 fxxbar
(1 row)

postgres=# select regexp_replace('foobar','b..','x');
 regexp_replace
----------------
 foox

--regexp_match 函数匹配返回文本数组
postgres=# select regexp_match('foobar','..b');
 regexp_match
--------------
 {oob}
(1 row)

postgres=# select regexp_match('foobar','(f)(..b)');
 regexp_match
--------------
 {f,oob}
(1 row)

--regexp_matches 函数匹配返回文本数组的集合
postgres=# select regexp_matches('foobarbequebazilbarfbonk','b[^b]+','g');
 regexp_matches
----------------
 {bar}
 {beque}
 {bazil}
 {barf}
 {bonk}
(5 rows)


--regexp_split_to_table 函数把一个 POSIX 正则表达式模式当作一个定界符来分离一个串
postgres=#  select regexp_split_to_table('shi yi shi','\s');
 regexp_split_to_table
-----------------------
 shi
 yi
 shi
(3 rows)