PostgreSQL模式匹配与正则表达式方法总结

1.1 PostgreSQL中的模式匹配和正则表达式

PostgreSQL提供了以下3种实现模式匹配的方法：

·传统SQL的LIKE操作符。
·SQL99标准新增的SIMILAR TO操作符。
·POSIX风格的正则表达式。

另外，还有一个模式匹配函数substring可用，它可以使用SIMILAR TO风格或者POSIX风格的正则表达式。后面会详细介绍substring函数的使用方法。

1.2 传统SQL的LIKE操作符

传统的LIKE操作符比较简单，其中百分号“%”代表0个或任意个字符，而下划线“_”代表任意一个字符，示例如下：

maxwelldb=# select * from mytab3 where note like \’ab_ab%\’;
id | note
—-+———-
1 | abcabefg
4 | ab_abefg
5 | ab%abefg
(3 rows)

如果想匹配字符串中的百分号“%”自身或下划线“_”自身怎么办呢？可以在字符串前加转义字符反斜杠“\\”，示例如下：

maxwelldb=# select * from mytab3 where note like \’%\\%%\’;
id | note
—-+———-
5 | ab%abefg
(1 row)

maxwelldb=# select * from mytab3 where note like \’%\\_%\’;
id | note
—-+———-
4 | ab_abefg
(1 row)

转义字符也可以通过ESCAPE子句指定为其他字符，如指定成“#”，命令如下：

maxwelldb=# select * from mytab3 where note like \’%#%%\’ escape \’#\’;
id | note
—-+———-
5 | ab%abefg
(1 row)

maxwelldb=# select * from mytab3 where note like \’%#_%\’ escape \’#\’;
id | note
—-+———-
4 | ab_abefg
(1 row)

maxwelldb=#

转义字符本身可以通过使用连续两个转义字符去除转义字符的特殊意义，示例如下：

maxwelldb=# insert into mytab3 value3(7,\’\\\’);
ERROR: syntax error at or near \”value3\”
LINE 1: insert into mytab3 value3(7,\’\\\’);
^
maxwelldb=# insert into mytab3 values(7,\’\\\’);
INSERT 0 1
maxwelldb=# insert into mytab3 values(8,\’#\’);
INSERT 0 1
maxwelldb=# select * from mytab3 where note like \’%\\\\%\’;
id | note
—-+——
7 | \\
(1 row)

maxwelldb=# select * from mytab3 where note like \’%##%\’ escape \’#\’;
id | note
—-+——
8 | #
(1 row)

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

PostgreSQL还提供了如下与LIKE等价的操作符。

·~~：等效于LIKE。

·~~*：等效于ILIKE。

·!~~：等效于NOT LIKE。

·!~~*：操作符NOT ILIKE。

1.3 SIMILAR TO正则表达式

SIMILAR TO是SQL99标准定义的正则表达式。SQL标准的正则表达式是混合了LIKE和普通的正则表达式的一个杂合体。

SIMILAR TO操作符只有匹配整个字符串时才能匹配成功，这一点与LIKE相同，而与普通的正则表达式只匹配部分的习惯不同。SIMILAR TO与LIKE一样也使用下划线和百分号分别匹配单个字符和任意字符串。

除了从LIKE借用的这些功能之外，SIMILAR TO还支持以下几个与POSIX正则表达式相同的模式匹配元字符。

·|：表示选择两个候选项之一。

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

·+：表示重复前面的项一次或更多次。

·?：表示重复前面的项0次或一次。

·{m}：表示重复前面的项m次。

·{m，}：表示重复前面的项m次或更多次。

·{m，n}：表示重复前面的项至少m次，不超过n次。

·括号()：可以作为项目分组到一个独立的逻辑项。

·[…]：声明一个字符类，就像POSIX正则表达式。

在SIMILAR TO中英文的句号“.”并不是元字符。和LIKE操作符一样，可以使用反斜杠关闭这些元字符所有的特殊含义，当然也可以用ESCAPE声明另外一个转义字符。

maxwelldb=# select \’osdba\’ SIMILAR TO \’a\’;
?column?
———-
f
(1 row)

maxwelldb=# select \’osdba\’ SIMILAR TO \’%(b|a)\’;
?column?
———-
t
(1 row)

maxwelldb=# select \’osdb\’ SIMILAR TO \’%(s|d)%\’;
?column?
———-
t
(1 row)

maxwelldb=#

1.4 POSIX正则表达式

POSIX正则表达式的模式匹配操作符有以下几个。

·~：匹配正则表达式，区分大小写。

·~*：匹配正则表达式，不区分大小写。

·!~：不匹配正则表达式，区分大小写。

·!~*：不匹配正则表达式，不区分大小写。

POSIX正则表达式提供了比LIKE和SIMILAR TO操作符更强大的模式匹配方法。许多UNIX的命令如egrep、sed、awk都使用类似的模式匹配语言。

maxwelldb=#
maxwelldb=# select \’osdba\’ ~ \’a\’;
?column?
———-
t
(1 row)

maxwelldb=# select \’osdba\’ ~ \'(b|a)*\’;
?column?
———-
t
(1 row)

maxwelldb=# select \’osdb\’ ~ \’.*(b|a).*\’;
?column?
———-
t
(1 row)

maxwelldb=# select \’osdba\’ ~ \'(s|d).*\’;
?column?
———-
t
(1 row)

maxwelldb=#

在POSIX正则表达式中，百分号与下划线没有像LIKE或SIMILAR TO操作符中的特殊意义，示例如下：

maxwelldb=# select \’osdba\’ ~ \’%\’;
?column?
———-
f
(1 row)

maxwelldb=# select \’osdba\’ ~ \’_sdba\’;
?column?
———-
f
(1 row)

maxwelldb=#

在POSIX正则表达式中与SIMILAR TO和LIKE操作符不一样的是，只要部分匹配到字符串就返回真，这与UNIX中的grep命令是一样的。

maxwelldb=# select \’osdba\’ ~ \’os\’;
?column?
———-
t
(1 row)

maxwelldb=#

如果想匹配开头或结尾，需要使用POSIX中的“^”或“$”元字符，示例如下：

maxwelldb=# select \’aosdba\’ ~ \’os\’;
?column?
———-
t
(1 row)

maxwelldb=# select \’aosdba\’ ~ \’^os\’;
?column?
———-
f
(1 row)

maxwelldb=# select \’osdba\’ ~ \’db\’;
?column?
———-
t
(1 row)

maxwelldb=# select \’osdba\’ ~ \’db$\’;
?column?
———-
f
(1 row)

maxwelldb=# select \’osdba\’ ~ \’dba$\’;
?column?
———-
t
(1 row)

maxwelldb=#

1.5 模式匹配函数substring

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

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

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

maxwelldb=# select substring(\’osdba\’,2);
substring
———–
sdba
(1 row)

maxwelldb=#

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

有两个参数且都是字符串，这是一种使用POSIX正则表达式的方式。

前面说过，在PostgreSQL中有两种正则表达式，一种被称为SQL正则表达式；另一种被称为POSIX正则表达式。POSIX正则表达式就是一般在脚本语言中使用的标准正则表达式，而SQL正则表达式首先是遵循SQL语句中的LIKE语法的，如字符“.”在POSIX正则表达式中代表任意字符，而在SQL表达式中就只能表示自己，表示任意字符的元字符是“_”。

SQL正则表达式中“%”可以表示任意个字符，而这在POSIX正则表达式中要用“.*”来表示。

同时SQL正则表达式也支持以下语法。

·|：表示选择（两个候选之一），这在POSIX正则表达式中不支持。

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

·+：表示重复前面的项一次或更多次。

·()：把项组合成一个逻辑项。

·[…]：声明一个字符类。

SIMILAR TO中使用的就是SQL正则表达式，而“~”使用的是POSIX正则表达式，注意两者间的如下区别：

maxwelldb=# select \’osdba\’ ~ \’sdb\’;
?column?
———-
t
(1 row)

maxwelldb=# select \’osdba\’ similar to \’sdb\’;
?column?
———-
f
(1 row)

maxwelldb=# select \’osdba\’ similar to \’%sdb%\’;
?column?
———-
t
(1 row)

maxwelldb=#