PostgreSQL 的模式匹配与正则表达式

一、PostgreSQL 实现模式匹配的方法

• LIKE
• SIMILAR TO
• POSIX 风格的正则表达式
• 模式匹配函数 substring

二、LIKE 操作符

只有在匹配整个字符串时返回真

符号	描述
%	任意0个或任意个字符
_	任意一个字符
\%	%
\_	_

postgres=# select * from test_zhengze;
 id |   text
----+-----------
  1 | abc%ab_AB
  2 | abcabefg
  3 | abxyz
  4 | 123abf
  5 | ab_sew
  6 | ab%sew
  7 | wxf#cd
(7 rows)

postgres=# select * from test_zhengze where text like '%123%';
 id |  text
----+--------
  4 | 123abf
(1 row)

postgres=# select * from test_zhengze where text like 'ab_sew';
 id |  text
----+--------
  5 | ab_sew
  6 | ab%sew
(2 rows)

postgres=# select * from test_zhengze where text like '%\%%';
 id |   text
----+-----------
  1 | abc%ab_AB
  6 | ab%sew
(2 rows)

postgres=# select * from test_zhengze where text like '__\_%';
 id |  text
----+--------
  5 | ab_sew
(1 row)

转义字符 \也可使用 ESCAPE 子句指定成其他的字符

postgres=# select * from test_zhengze where text like '%$%%' escape '$';
 id |   text
----+-----------
  1 | abc%ab_AB
  6 | ab%sew
(2 rows)

• ~~ 等效于 LIKE
• ~~* 等效于 ILIKE，不区分大小写
• !~~ 等效于 NOT LIKE
• !~~* 等效于 NOT ILIKE

三、SIMILAR TO 正则表达式

只有在匹配整个字符串时返回真

符号	描述
_	单个字符
%	任意字符串
|	选择两个候选项之一
*	重复前面的项 >= 0
+	重复前面的项 >=1
?	重复前面的项 0 或 1 次
{m}	重复前面的项 m 次
{m, }	重复前面的项 >=m 次
{m,n}	重复前面的项 >=m，<=n 次
()	可作为项目分组到一个独立的逻辑项
[...]	声明一个字符类

postgres=# select 'osdba' similar to 'a';
 ?column?
----------
 f
(1 row)

postgres=# select 'osdba' similar to '%(a|b)';
 ?column?
----------
 t
(1 row)

postgres=# select 'osdba' similar to '(s|b)%';
 ?column?
----------
 f
(1 row)

四、POSIX 正则表达式

只要部分匹配到字符串就返回真，类似于egrep sed awk

符号	描述
.	任意字符

 

匹配操作符	描述
~	匹配正则表达式，区分大小写
~*	匹配正则表达式，不区分大小写
!~	不匹配正则表达式，区分大小写
!~*	不匹配正则表达式，不区分大小写

postgres=# select 'osdba' ~ 'a';
 ?column?
----------
 t
(1 row)

-- 匹配开头:^ ；匹配结尾:$ 
postgres=# select 'osdba' ~ '^os';
 ?column?
----------
 t
(1 row)

postgres=# select 'osdba' ~ 'ba$';
 ?column?
----------
 t
(1 row)

五、模式匹配函数 substring

1. subdtring(<字符串>,,[n]) ：从第 m 个字符开始[，截取 n 个字符]

   postgres=# select substring('osdba',2);
    substring
   -----------
    sdba
   (1 row)
   
   postgres=# select substring('osdba',3,2);
    substring
   -----------
    db
   (1 row)
   

2. substring(<字符串>,<字符串>)

   使用 POSIX 正则表达式

   postgres=# select substring('osdba-6-osdba',E'(\\d+)');
    substring
   -----------
    6
   (1 row)
   

3. substring(<字符串>,<字符串>,<字符串>) 或 subtring(<字符串>from<字符串>for<字符串>)

   使用 SQL 正则表达式

   postgres=# select substring('osdba-6-osdba','%$"[0-9]+$"%','$');
    substring
   -----------
    6
   (1 row)