PostgreSQL 模式匹配和正则表达式简介

发表于： 2023-08-08 14:38:39
分类：网络资源
浏览：浏览(46)

适用范围

建议PG 15及以上版本

方案概述

PostgreSQL 提供了下面几种模式匹配的方法：

传统SQL的like操作符
SQL99标准新增的similar to 操作符
posix风格的正在表达式
模式匹配函数substring可以使用similar to风格或者posix风格的正则表达式

实施步骤

1.like操作符

传统的like操作符中百分号 “%” 代表0个或任意个字符，而下划线 “_” 代表任意一个字符。

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

1.1.基础数据

postgres=# select * from test order by 1;
 id |   name   
----+----------
  1 | abcdef
  2 | ab_cdef
  3 | abCDef
  4 | ab%cdef
  5 | ababcdef
  6 | abcxyz
  7 | abcd\fab
  8 | abab#ab
  9 | ababab$
 10 | $ababab
(10 rows)

1.2.匹配0个或多个字符

postgres=# select * from test where name like 'ab%';
 id |   name   
----+----------
  1 | abcdef
  2 | ab_cdef
  4 | ab%cdef
  5 | ababcdef
  6 | abcxyz
  9 | ababab$
  7 | abcd\fab
  8 | abab#ab
  3 | abCDef
(9 rows)

1.3.匹配1个字符

postgres=# select * from test where name like 'ababab_';
 id |  name   
----+---------
  9 | ababab$
(1 row)

1.4.匹配字符串中的 “%” 或者 “_”

需要使用转义字符反斜杠 “\” 转移通配符

postgres=# select * from test where name like '%\%%'; --不加 \ 匹配表中所有数据
 id |  name   
----+---------
  4 | ab%cdef
(1 row)

postgres=# select * from test where name like '%\_%'; --不加 \ 匹配表中所有数据
 id |  name   
----+---------
  2 | ab_cdef
(1 row)

转义字符默认是 "\" ,可以通过escape指定其他的字符为转移字符
postgres=# select * from test where name like '%#%%' escape '#'; --指定 # 为转义字符
 id |  name   
----+---------
  4 | ab%cdef
(1 row)

postgres=# select * from test where name like '%$_%' escape '$'; --指定 $ 为转义字符
 id |  name   
----+---------
  2 | ab_cdef
(1 row)

1.5.匹配转义字符

如果需要匹配字符串中的转义字符需要连接使用2次转义字符来去除转义字符的功能

postgres=# select * from test where name like '%\\%'; --连续使用两次 \ 进行转换
 id |   name   
----+----------
  7 | abcd\fab
(1 row)

postgres=# select * from test where name like '%##%' escape '#'; --字符 # 是转义字符时获取name中含有 # 的数据
 id |  name   
----+---------
  8 | abab#ab
(1 row)

postgres=# select * from test where name like '%#%';  --字符 # 不是转义字符时获取name中含有 # 的数据，和上条SQL进行对比学习
 id |  name   
----+---------
  8 | abab#ab
(1 row)

1.6.表达式中使用like

postgres=# select 'postgre' like 'po%';
 ?column? 
----------
 t
(1 row)

1.7.匹配大小写ilike

PG中提供了标准SQL中没有的可以忽略大小写进行匹配的ilike

postgres=# select * from test where name ilike 'abc%ef';
 id |  name  
----+--------
  1 | abcdef
  3 | abCDef
(2 rows)

1.8. ~~ 等效 like

postgres=# select * from test where name ~~ 'abcd%';
 id |   name   
----+----------
  1 | abcdef
  7 | abcd\fab
(2 rows)

1.9. ~~* 等效 ilike

postgres=# select * from test where name ~~* 'abcd%';
 id |   name   
----+----------
  1 | abcdef
  7 | abcd\fab
  3 | abCDef
(3 rows)

1.10. !~~ 等效 not like

postgres=# select * from test where name !~~ 'abc%';
 id |   name   
----+----------
  2 | ab_cdef
  4 | ab%cdef
  5 | ababcdef
  9 | ababab$
 10 | $ababab
  8 | abab#ab
  3 | abCDef
(7 rows)

1.11. !~~* 等效 not ilike

postgres=# select * from test where name !~~* 'abc%';
 id |   name   
----+----------
  2 | ab_cdef
  4 | ab%cdef
  5 | ababcdef
  9 | ababab$
 10 | $ababab
  8 | abab#ab
(6 rows)

2.similar to正则表达式

similar to 是SQL99标准定义的正则表达式。SQL标准的正则表达式是混合了like和普通的正则表达式。
similar to操作符只有匹配整个字符串时才能匹配成功，这一点与like相同，而与普通的正则表达式只匹配部分的习惯不同。similar to与like一样也使用下划线和百分号分别匹配单个字符和任意字符串。
除了从like借用的这些功能之外，similar to还支持几个与posix正则表达式相同的模式匹配元字符。

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

|  提供两个选项，可以理解为 or
*  表示重复前面的项0次或多次
+  表示重复前面的项1次或多次
?  表示重复前面的项0次或1次
{m}  表示重复前面的项m次
{m,} 表示重复前面的项m次或更多次
{m,n}  表示重复前面的项至少m次，至多n次
() 用于将项目分组到单个逻辑项目中
[...] 指定字符类,仅用于POSIX 正则表达式中

2.1.匹配0个或多个字符

postgres=# select * from test where name similar to 'abc%';
 id |   name   
----+----------
  1 | abcdef
  6 | abcxyz
  7 | abcd\fab
(3 rows)

postgres=# select * from test where name similar to 'abc[a-z,\\]*';
 id |   name   
----+----------
  1 | abcdef
  6 | abcxyz
  7 | abcd\fab
(3 rows)

2.2.匹配1个字符

postgres=# select * from test where name similar to 'abcxy_';
 id |  name  
----+--------
  6 | abcxyz
(1 row)

postgres=# select * from test where name similar to 'abcxy[a-z]?';
 id |  name  
----+--------
  6 | abcxyz
(1 row)

2.3.匹配两个选项之一

postgres=# select * from test where name similar to '%(b|z)';
 id |   name   
----+----------
  6 | abcxyz
 10 | $ababab
  7 | abcd\fab
  8 | abab#ab
(4 rows)

2.4.匹配多个字符

postgres=# select * from test where name similar to '[a-z]{6}';
 id |  name  
----+--------
  1 | abcdef
  6 | abcxyz
(2 rows)

postgres=# select * from test where name similar to '[a-z]{6,}';
 id |   name   
----+----------
  1 | abcdef
  5 | ababcdef
  6 | abcxyz
(3 rows)

postgres=# select * from test where name similar to '[a-z,%]{6,}';
 id |   name   
----+----------
  1 | abcdef
  4 | ab%cdef
  5 | ababcdef
  6 | abcxyz
(4 rows)

postgres=# select * from test where name similar to '([a-z_]{6,}|[a-z,%]{7,})';
 id |   name   
----+----------
  1 | abcdef
  2 | ab_cdef
  4 | ab%cdef
  5 | ababcdef
  6 | abcxyz
(5 rows)

postgres=# select * from test where name similar to '[a-z]+';
 id |   name   
----+----------
  1 | abcdef
  5 | ababcdef
  6 | abcxyz
(3 rows)

3.posix正则表达式

POSIX正则表达式提供了比LIKE和SIMILAR TO操作符更强大的模式匹配方法。许多UNIX的命令如egrep、sed、awk都使用类似的模式匹配语言；在POSIX正则表达式中，百分号与下划线没有像LIKE或SIMILAR TO操作符中的特殊意义;在POSIX正则表达式中与SIMILAR TO和LIKE操作符不一样的是，只要部分匹配到字符串就返回真，这与UNIX中的grep命令是一样的。
。

POSIX正则表达式的模式匹配操作符有以下几个。
~   匹配正则表达式，区分大小写。
~*  匹配正则表达式，不区分大小写。
!~  不匹配正则表达式，区分大小写。
!~* 不匹配正则表达式，不区分大小写。

3.1.POSIX中 ~ 或者 _ 测试

POSIX中 ~ 或者 _ 没有不代表通配符,只是普通的字符

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

postgres=# select * from test where name ~ 'abcxy_';
 id | name 
----+------
(0 rows)

3.2.匹配含有abc字符

postgres=# select * from test where name ~ 'abc';
 id |   name   
----+----------
  1 | abcdef
  5 | ababcdef
  6 | abcxyz
  7 | abcd\fab
(4 rows)

3.3.匹配 ^ 或者 $ 结尾的

postgres=# select * from test where name ~ '^aba';
 id |   name   
----+----------
  5 | ababcdef
  8 | abab#ab
  9 | ababab$
(3 rows)

postgres=# select * from test where name ~ 'f$';
 id |   name   
----+----------
  1 | abcdef
  2 | ab_cdef
  3 | abCDef
  4 | ab%cdef
  5 | ababcdef
(5 rows)

3.4.不区分大小写匹配含有abc字符

postgres=# select * from test where name ~* 'abc';
 id |   name   
----+----------
  1 | abcdef
  3 | abCDef
  5 | ababcdef
  6 | abcxyz
  7 | abcd\fab
(5 rows)

3.5.不匹配含有abc字符串

postgres=# select * from test where name !~ 'abc';
 id |  name   
----+---------
  2 | ab_cdef
  3 | abCDef
  4 | ab%cdef
  8 | abab#ab
  9 | ababab$
 10 | $ababab
(6 rows)

3.6.不区分大小写不匹配含有abc字符串

postgres=# select * from test where name !~* 'abc';
 id |  name   
----+---------
  2 | ab_cdef
  4 | ab%cdef
  8 | abab#ab
  9 | ababab$
 10 | $ababab
(5 rows)

4.模式匹配函数substring

PostgreSQL中有一个很强大的函数substring，该函数可以使用正则表达式。substring有以下3种用法。

4.1.第一种：substring（<字符串>,<数字>,[数字]）

后两个参数为数字，该函数和其他语言中的substr函数的含义相同，示例如下。

postgres=# select substring(name,3,6),name from test;
 substring |   name   
-----------+----------
 cdef      | abcdef
 _cdef     | ab_cdef
 CDef      | abCDef
 %cdef     | ab%cdef
 abcdef    | ababcdef
 cxyz      | abcxyz
 cd\fab    | abcd\fab
 ab#ab     | abab#ab
 abab$     | ababab$
 babab     | $ababab
(10 rows)

4.2.第二种：substring（<字符串>,<字符串>）

有两个参数且都是字符串，这是一种使用POSIX正则表达式的方式。前面说过，在PostgreSQL中有两种正则表达式，一种被称为SQL正则表达式；另一种被称为POSIX正则表达式。POSIX正则表达式就是一般在脚本语言中使用的标准正则表达式，而SQL正则表达式首先是遵循SQL语句中的LIKE语法的，如字符“.”在POSIX正则表达式中代表任意字符，而在SQL表达式中就只能表示自己，表示任意字符的元字符是“_”。
SQL正则表达式中“%”可以表示任意个字符，而这在POSIX正则表达式中要用“.*”来表示。
同时SQL正则表达式也支持以下语法。
|：表示选择（两个候选之一），这在POSIX正则表达式中不支
持。

表示重复前面的项0次或更多次。

表示重复前面的项一次或更多次。
() 把项组合成一个逻辑项。
[…] 声明一个字符类。
SIMILAR TO中使用的就是SQL正则表达式，而“~”使用的是POSIX正则表达式，注意两者间的如下区别：

postgres=# select substring(name,'abc.*f$'),name from test;
 substring |   name   
-----------+----------
 abcdef    | abcdef
           | ab_cdef
           | abCDef
           | ab%cdef
 abcdef    | ababcdef
           | abcxyz
           | abcd\fab
           | abab#ab
           | ababab$
           | $ababab
(10 rows)

4.3.第三种：substring（<字符串>,<字符串>,<字符串）

substring（<字符串>,<字符串>,<字符串）或substring（<字符串> from <字符串> for <字符串）

substring(string, pattern, escape-character)
substring(string from pattern for escape-character)
substring(string similar pattern escape escape-character)

postgres=# select substring(name,'%$"[abcd]+$"%','$'),name from test;
 substring |   name   
-----------+----------
 abcd      | abcdef
 ab        | ab_cdef
 ab        | abCDef
 ab        | ab%cdef
 ababcd    | ababcdef
 abc       | abcxyz
 abcd      | abcd\fab
 abab      | abab#ab
 ababab    | ababab$
 ababab    | $ababab
(10 rows)

postgres=# select substring(name from '%$"[abcd]+$"%' for '$'),name from test;
 substring |   name   
-----------+----------
 abcd      | abcdef
 ab        | ab_cdef
 ab        | abCDef
 ab        | ab%cdef
 ababcd    | ababcdef
 abc       | abcxyz
 abcd      | abcd\fab
 abab      | abab#ab
 ababab    | ababab$
 ababab    | $ababab
(10 rows)

postgres=# select substring(name similar '%$"[abcd]+$"%' escape '$'),name from test;
 substring |   name   
-----------+----------
 abcd      | abcdef
 ab        | ab_cdef
 ab        | ab%cdef
 ababcd    | ababcdef
 abc       | abcxyz
 ababab    | ababab$
 ababab    | $ababab
 abcd      | abcd\fab
 abab      | abab#ab
 ab        | abCDef
(10 rows)

参考文档

《PostgreSQL 修炼之道从小工到专家》

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论