PostgreSql 字符串匹配

一、概述

  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 匹配操作符

4.2 常用匹配规则

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)