PgSQL中的模式匹配
2023-05-14 21:25:52 
阿炯
PostgreSQL中主要提供了三种独立的实现模式匹配的方法：SQL LIKE操作符、更近一些的SIMILAR TO操作符（SQL:1999 里添加进来的）和POSIX或Perl风格的正则表达式；LIKE搜索比其他两种选项简单得多，但在功能上也是较弱的。
参考来源：第 9 章 函数和操作符下的9.7. 模式匹配，最新可参考官方英文原文。
1. LIKE
LIKE模式匹配总是覆盖整个串。因此要匹配在串内任何位置的序列，该模式必须以百分号开头和结尾。要匹配文本的下划线或者百分号，而不是匹配其它字符，在pattern里相应的字符必须前导逃逸字符。在pattern里相应的字符必须前导逃逸字符，缺省的逃逸字符是反斜线，但可以用ESCAPE子句指定一个不同的逃逸字符。要匹配逃逸字符本身，写两个逃逸字符。关键字和符号有：LIKE, ESCAPE, ILIKE, ~, ~*, !~, !~*, ~~,~~* 等等。~ 用于正则匹配(Perl中的专用于正则匹配的符号)，SIMILAR TO是前面两者的综合。(LIKE我们常用的模糊查询；ILIKE不区分大小写的模糊查询；SIMILAR TO可以使用正则表达式的模糊查询)。
关键字ILIKE可以用于替换LIKE，令该匹配根据活动区域成为大小写无关。此不属于SQL标准而是一个PostgreSQL扩展。操作符~~等效于LIKE，而~~*对应ILIKE；还有 !~~和!~~*操作符分别代表NOT LIKE和NOT ILIKE。所有这些操作符都是PostgreSQL特有的：
'~~' 和 LIKE 等效。'!~~' 和 NOT LIKE 等效。
'~~*' 和 ILIKE 等效。'!~~*' 和 'NOT ILIKE' 等效。
LIKE和 ILIKE通常与通配符结合使用，通配符表示任意字符，在 PostgreSQL 中主要有以下两种通配符：
%：百分号用于匹配字符串序列，可匹配任意组合，多字符占位符；
_：下划线用于匹配任何单一字符。
如果没有使用以上两种通配符，LIKE 子句和等号'='得到的结果是一样的。~会在下文匹配正则处讲解。
ESCAPE
Select* from test where varchar like 'b_a%'escape 'b'; varchar 匹配任何以'_a'开头的字符串。
ESCAPE 'escape_character' 允许在字符串中搜索通配符而不是将其作为通配符使用。
注意：反斜杠在字符串文本里已经有特殊含义了，所以如果写一个包含反斜杠的模式常量，那就要在 SQL 语句里写两个反斜杠。因此写一个匹配单个反斜杠的模式实际上要在语句里写四个反斜杠。可以通过用 ESCAPE 选择一个不同的逃逸字符来避免这样；这样反斜杠就不再是 LIKE 的特殊字符了。但仍然是字符文本分析器的特殊字符，所以还是需要两个反斜杠。也可以通过写成 ESCAPE '' 的方式关闭逃逸机制，这时就不能关闭下划线和百分号的特殊含义。
如查找数据库中表的名字(查找以sys_开头的表的名字)：select tablename from pg_tables where tablename like 'sys!_%' ESCAPE '!';
利用Escape转义: 上面的sql语句中就是将!作为转义字符，作用是将"_"转换成普通的"_"。
2. SIMILAR TO
SIMILAR TO操作符根据自己的模式是否匹配给定串而返回真或者假，和LIKE非常类似，只不过它使用 SQL 标准定义的正则表达式理解模式。是在LIKE标记和普通的正则表达式标记的奇怪的杂交。
类似LIKE，SIMILAR TO操作符只有在它的模式匹配整个串的时候才能成功；这一点和普通的正则表达式的行为不同，在普通的正则表达式里，模式匹配串的任意部分。和LIKE类似的地方还有SIMILAR TO使用_和%作为分别代表任意单个字符和任意串的通配符（这些可以比得上 POSIX 正则表达式里的.和.*），另外它还从 POSIX 正则表达式借用了部分模式。
带有三个参数的substring函数可以提取匹配SQL正则表达式模式的子字符串。该函数可以按照SQL99语法编写。
substring(string From pattern For escape-character)
或作为一个普通的三参数函数
substring(string, pattern, escape-character)
与SIMILAR TO一样，指定的模式必须与整个数据字符串匹配，否则函数失败并返回空值。为了表示匹配的数据子字符串的模式中，模式中应该包含两个转义字符的出现，并在后面加上一个双引号(")。匹配成功后，将返回与这些分隔符之间的模式部分匹配的文本。
作为对SQL标准的扩展，PostgreSQL只允许有一个转义双引号分隔符，在这种情况下，第三个正则表达式被视为空；或者没有分隔符，在这种情况下，第一个和第三个正则表达式被视为空。一些例子，使用#"定界返回串：
substring('foobar' from '%#"o_b#"%' for '#') oob
substring('foobar' from '#"o_b#"%' for '#') NULL
substring('freeoa' from '%|"e_a|"' for '|') eoa
非常新的版本还支持如下的写法：
substring('foobar' similar '%#"o_b#"%' escape '#') oob
substring('foobar' similar '#"o_b#"%' escape '#') NULL
3. POSIX正则表达式
POSIX正则表达式提供了比LIKE和SIMILAR TO操作符更强大的含义。正则表达式是一个字符序列，是定义一个串集合（一个正则集）的缩写。如果一个串是正则表达式描述的正则集中的一员时，就说这个串匹配该正则表达式。和LIKE一样，模式字符准确地匹配串字符，除非在正则表达式语言里有特殊字符 — 不过正则表达式用的特殊字符和LIKE用的不同。和LIKE模式不一样的是，正则表达式允许匹配串里的任何位置，除非该正则表达式显式地挂接在串的开头或者结尾。
POSIX正则表达式的模式匹配操作符有以下几种：
~：匹配正则表达式，区分大小写。
~*：匹配正则表达式，不分大小写。
!~：不匹配正则表达式，区分大小写。
!~*：不匹配正则表达式，不分大小写。
regexp_replace
该函数提供了将匹配 POSIX 正则表达式模式的子串替换为新文本的功能。语法是 regexp_replace(source, pattern, replacement [, flags ])。如果没有匹配pattern，那么返回不加修改的source串。如果有匹配，则返回的source串里面的匹配子串将被replacement串替换掉。replacement串可以包含\n，其中\n是 1 到 9，表明源串里匹配模式里第n个圆括号子表达式的子串应该被插入，并且它可以包含\&表示应该插入匹配整个模式的子串。如果你需要放一个文字形式的反斜线在替换文本里，那么写\\。flags参数是一个可选的文本串，它包含另个或更多单字母标志，这些标志可以改变函数的行为。标志i指定大小写无关的匹配，而标志g指定替换每一个匹配的子串而不仅仅是第一个。
regexp_match
返回一个文本数组，它包含一个POSIX正则表达式模式与一个字符串第一个匹配所得到的子串。其语法是regexp_match(string, pattern [, flags ])。如果没有匹配，则结果为NULL。如果找到一个匹配并且pattern不包含带括号的子表达式，那么结果是一个单一元素的文本数组，其中包含匹配整个模式的子串。如果找到一个匹配并且pattern含有带括号的子表达式，那么结果是一个文本数组，其中第n个元素是与pattern的第n个圆括号子表达式匹配的子串（“非捕获”圆括号不计入在内）。flags参数是一个可选的文本字符串，它包含零个或者更多个可以改变该函数行为的单字母标志。
regexp_split_to_table
把一个 POSIX 正则表达式模式当作一个定界符来分离一个串。语法形式是regexp_split_to_table(string, pattern [, flags ])。如果没有与pattern的匹配，该函数返回string。如果有至少有一个匹配，对每一个匹配它都返回从上一个匹配的末尾（或者串的开头）到这次匹配开头之间的文本。当没有更多匹配时，它返回从上一次匹配的末尾到串末尾之间的文本。flags参数是一个可选的文本串，它包含零个或更多单字母标志，这些标识可以改变该函数的行为。
regexp_split_to_array
的行为和regexp_split_to_table相同，不过regexp_split_to_array会把它的结果以一个text数组的形式返回。它的语法是regexp_split_to_array(string, pattern [, flags ])。这些参数和regexp_split_to_table的相同。
PostgreSQL uses POSIX or “Portable Operating System Interface for Unix” regular expressions, which are better than LIKE and SIMILAR TO operators used for pattern matching.
4. Perl兼容正则匹配符(~)
可以使用正则模式来匹配字符串，可以认为是LIKE的扩展。笔者认为是目前运行最好的正则系统。
select * from table where ~ 'ab';
代表着可以匹配任何包含ab的字符串其实就相当于省略的两边的.*，相当于LIKE前后的%符号。
如果想要做前缀或后缀匹配，可以用下面的方法：
前缀+模糊查询
select * from table where ~ '^ab';
后缀+模糊查询
select * from table where ~ 'ab$';
5. 实测
CREATE TABLE freeoa.email (
id serial4 PRIMARY KEY,
"name" varchar(32) NOT NULL,
email text NULL
);
INSERT INTO freeoa.email ("name",email) VALUES
('Pratibha','pratibha67@sqlguide.edu'),
('Srishti','srishti@sqlguide.edu'),
('Madhav','madhav@tsinfo.edu'),
('Radhika','radhika41@spguide.com'),
('Bhavya','bhavyaa@perlguide.com'),
('Disha','disha@Sqlguide.edu'),
('Divanshi','divan2shi3@spguide.com'),
('Kartik','kartik@PerlGuide.com'),
('Rytham','rytham6@sqlGuide.edu'),
('Tanisha','tanisha@freeoa.net');
1).使用'*'进行大小写的适配
SELECT * FROM Email WHERE Email ~* 'sqlGuide';
2).使用'!'进行反向的适配
SELECT * FROM Email WHERE Email !~ 'sqlguide';
3).使用'^'进行字串头、尾部适配
SELECT * FROM Email WHERE Email ~ '^d';
SELECT * FROM Email WHERE Email ~ 'edu$';
4).使用'\d'进行数字的适配
SELECT * FROM Email WHERE Email ~ '\d';
SELECT * FROM Email WHERE Email ~ '[0-9]';
SELECT * FROM Email WHERE Email ~ '\d+';
SELECT * FROM Email WHERE Email ~ '[0-9]+';
SELECT * FROM Email WHERE Email ~ '[0-9][0-9]';
SELECT * FROM Email WHERE Email ~ '\d{2,}';
5).匹配与替换
将首个出现数字替换为'*'
SELECT REGEXP_REPLACE(Email,'[0-9]','*') From Email;
将首个连续出现数字替换为'*'
SELECT REGEXP_REPLACE(Email,'[0-9]+','*') From Email;
将所有出现数字替换为'*'
SELECT REGEXP_REPLACE(Email,'[0-9]','*','g') From Email;
将非数字替换为空
SELECT REGEXP_REPLACE(Email,'\D','','g') FROM Email;
SELECT REGEXP_REPLACE(Email, '[^\w]+','','g') FROM Email;
这里的'g'就是模式匹配选项(Flag)。
Flag 'i': match case-insensitively.
Flag 'g': search globally for each occurrence.
6).返回邮件所在的域名集合
SELECT REGEXP_MATCHES(Email,'.+@(.*)$') FROM Email;
SELECT REGEXP_MATCHES(Email,'.+@(.*)\.(\w+)') as domain FROM Email;
SELECT REGEXP_MATCHES(Email,'.+@(.*)\.(\w+)') as domain FROM Email;
SELECT REGEXP_MATCHES(Email,'(\w+)@(\w+)\.(\w+)') as domain FROM Email;
SELECT (REGEXP_MATCHES(Email,'(\w+)@(\w+)\.(\w+)'))[3] as domaintld FROM Email;
7).取子串
SELECT DISTINCT SUBSTRING(Email FROM '.+@(.*)$') FROM Email;
SELECT SUBSTRING(Email FROM '.+@(\w+)\.(\w+)') FROM Email;
正则匹配返回可能是0或多个集合或行，在第13版本中测试还发现安全支持中文匹配。
参考来源：第 9 章 函数和操作符下的9.7. 模式匹配，最新可参考官方英文原文。
1. LIKE
LIKE模式匹配总是覆盖整个串。因此要匹配在串内任何位置的序列，该模式必须以百分号开头和结尾。要匹配文本的下划线或者百分号，而不是匹配其它字符，在pattern里相应的字符必须前导逃逸字符。在pattern里相应的字符必须前导逃逸字符，缺省的逃逸字符是反斜线，但可以用ESCAPE子句指定一个不同的逃逸字符。要匹配逃逸字符本身，写两个逃逸字符。关键字和符号有：LIKE, ESCAPE, ILIKE, ~, ~*, !~, !~*, ~~,~~* 等等。~ 用于正则匹配(Perl中的专用于正则匹配的符号)，SIMILAR TO是前面两者的综合。(LIKE我们常用的模糊查询；ILIKE不区分大小写的模糊查询；SIMILAR TO可以使用正则表达式的模糊查询)。
关键字ILIKE可以用于替换LIKE，令该匹配根据活动区域成为大小写无关。此不属于SQL标准而是一个PostgreSQL扩展。操作符~~等效于LIKE，而~~*对应ILIKE；还有 !~~和!~~*操作符分别代表NOT LIKE和NOT ILIKE。所有这些操作符都是PostgreSQL特有的：
'~~' 和 LIKE 等效。'!~~' 和 NOT LIKE 等效。
'~~*' 和 ILIKE 等效。'!~~*' 和 'NOT ILIKE' 等效。
LIKE和 ILIKE通常与通配符结合使用，通配符表示任意字符，在 PostgreSQL 中主要有以下两种通配符：
%：百分号用于匹配字符串序列，可匹配任意组合，多字符占位符；
_：下划线用于匹配任何单一字符。
如果没有使用以上两种通配符，LIKE 子句和等号'='得到的结果是一样的。~会在下文匹配正则处讲解。
ESCAPE
Select* from test where varchar like 'b_a%'escape 'b'; varchar 匹配任何以'_a'开头的字符串。
ESCAPE 'escape_character' 允许在字符串中搜索通配符而不是将其作为通配符使用。
注意：反斜杠在字符串文本里已经有特殊含义了，所以如果写一个包含反斜杠的模式常量，那就要在 SQL 语句里写两个反斜杠。因此写一个匹配单个反斜杠的模式实际上要在语句里写四个反斜杠。可以通过用 ESCAPE 选择一个不同的逃逸字符来避免这样；这样反斜杠就不再是 LIKE 的特殊字符了。但仍然是字符文本分析器的特殊字符，所以还是需要两个反斜杠。也可以通过写成 ESCAPE '' 的方式关闭逃逸机制，这时就不能关闭下划线和百分号的特殊含义。
如查找数据库中表的名字(查找以sys_开头的表的名字)：select tablename from pg_tables where tablename like 'sys!_%' ESCAPE '!';
利用Escape转义: 上面的sql语句中就是将!作为转义字符，作用是将"_"转换成普通的"_"。
2. SIMILAR TO
SIMILAR TO操作符根据自己的模式是否匹配给定串而返回真或者假，和LIKE非常类似，只不过它使用 SQL 标准定义的正则表达式理解模式。是在LIKE标记和普通的正则表达式标记的奇怪的杂交。
类似LIKE，SIMILAR TO操作符只有在它的模式匹配整个串的时候才能成功；这一点和普通的正则表达式的行为不同，在普通的正则表达式里，模式匹配串的任意部分。和LIKE类似的地方还有SIMILAR TO使用_和%作为分别代表任意单个字符和任意串的通配符（这些可以比得上 POSIX 正则表达式里的.和.*），另外它还从 POSIX 正则表达式借用了部分模式。
带有三个参数的substring函数可以提取匹配SQL正则表达式模式的子字符串。该函数可以按照SQL99语法编写。
substring(string From pattern For escape-character)
或作为一个普通的三参数函数
substring(string, pattern, escape-character)
与SIMILAR TO一样，指定的模式必须与整个数据字符串匹配，否则函数失败并返回空值。为了表示匹配的数据子字符串的模式中，模式中应该包含两个转义字符的出现，并在后面加上一个双引号(")。匹配成功后，将返回与这些分隔符之间的模式部分匹配的文本。
作为对SQL标准的扩展，PostgreSQL只允许有一个转义双引号分隔符，在这种情况下，第三个正则表达式被视为空；或者没有分隔符，在这种情况下，第一个和第三个正则表达式被视为空。一些例子，使用#"定界返回串：
substring('foobar' from '%#"o_b#"%' for '#') oob
substring('foobar' from '#"o_b#"%' for '#') NULL
substring('freeoa' from '%|"e_a|"' for '|') eoa
非常新的版本还支持如下的写法：
substring('foobar' similar '%#"o_b#"%' escape '#') oob
substring('foobar' similar '#"o_b#"%' escape '#') NULL
3. POSIX正则表达式
POSIX正则表达式提供了比LIKE和SIMILAR TO操作符更强大的含义。正则表达式是一个字符序列，是定义一个串集合（一个正则集）的缩写。如果一个串是正则表达式描述的正则集中的一员时，就说这个串匹配该正则表达式。和LIKE一样，模式字符准确地匹配串字符，除非在正则表达式语言里有特殊字符 — 不过正则表达式用的特殊字符和LIKE用的不同。和LIKE模式不一样的是，正则表达式允许匹配串里的任何位置，除非该正则表达式显式地挂接在串的开头或者结尾。
POSIX正则表达式的模式匹配操作符有以下几种：
~：匹配正则表达式，区分大小写。
~*：匹配正则表达式，不分大小写。
!~：不匹配正则表达式，区分大小写。
!~*：不匹配正则表达式，不分大小写。
regexp_replace
该函数提供了将匹配 POSIX 正则表达式模式的子串替换为新文本的功能。语法是 regexp_replace(source, pattern, replacement [, flags ])。如果没有匹配pattern，那么返回不加修改的source串。如果有匹配，则返回的source串里面的匹配子串将被replacement串替换掉。replacement串可以包含\n，其中\n是 1 到 9，表明源串里匹配模式里第n个圆括号子表达式的子串应该被插入，并且它可以包含\&表示应该插入匹配整个模式的子串。如果你需要放一个文字形式的反斜线在替换文本里，那么写\\。flags参数是一个可选的文本串，它包含另个或更多单字母标志，这些标志可以改变函数的行为。标志i指定大小写无关的匹配，而标志g指定替换每一个匹配的子串而不仅仅是第一个。
regexp_match
返回一个文本数组，它包含一个POSIX正则表达式模式与一个字符串第一个匹配所得到的子串。其语法是regexp_match(string, pattern [, flags ])。如果没有匹配，则结果为NULL。如果找到一个匹配并且pattern不包含带括号的子表达式，那么结果是一个单一元素的文本数组，其中包含匹配整个模式的子串。如果找到一个匹配并且pattern含有带括号的子表达式，那么结果是一个文本数组，其中第n个元素是与pattern的第n个圆括号子表达式匹配的子串（“非捕获”圆括号不计入在内）。flags参数是一个可选的文本字符串，它包含零个或者更多个可以改变该函数行为的单字母标志。
regexp_split_to_table
把一个 POSIX 正则表达式模式当作一个定界符来分离一个串。语法形式是regexp_split_to_table(string, pattern [, flags ])。如果没有与pattern的匹配，该函数返回string。如果有至少有一个匹配，对每一个匹配它都返回从上一个匹配的末尾（或者串的开头）到这次匹配开头之间的文本。当没有更多匹配时，它返回从上一次匹配的末尾到串末尾之间的文本。flags参数是一个可选的文本串，它包含零个或更多单字母标志，这些标识可以改变该函数的行为。
regexp_split_to_array
的行为和regexp_split_to_table相同，不过regexp_split_to_array会把它的结果以一个text数组的形式返回。它的语法是regexp_split_to_array(string, pattern [, flags ])。这些参数和regexp_split_to_table的相同。
PostgreSQL uses POSIX or “Portable Operating System Interface for Unix” regular expressions, which are better than LIKE and SIMILAR TO operators used for pattern matching.
4. Perl兼容正则匹配符(~)
可以使用正则模式来匹配字符串，可以认为是LIKE的扩展。笔者认为是目前运行最好的正则系统。
select * from table where ~ 'ab';
代表着可以匹配任何包含ab的字符串其实就相当于省略的两边的.*，相当于LIKE前后的%符号。
如果想要做前缀或后缀匹配，可以用下面的方法：
前缀+模糊查询
select * from table where ~ '^ab';
后缀+模糊查询
select * from table where ~ 'ab$';
5. 实测
CREATE TABLE freeoa.email (
id serial4 PRIMARY KEY,
"name" varchar(32) NOT NULL,
email text NULL
);
INSERT INTO freeoa.email ("name",email) VALUES
('Pratibha','pratibha67@sqlguide.edu'),
('Srishti','srishti@sqlguide.edu'),
('Madhav','madhav@tsinfo.edu'),
('Radhika','radhika41@spguide.com'),
('Bhavya','bhavyaa@perlguide.com'),
('Disha','disha@Sqlguide.edu'),
('Divanshi','divan2shi3@spguide.com'),
('Kartik','kartik@PerlGuide.com'),
('Rytham','rytham6@sqlGuide.edu'),
('Tanisha','tanisha@freeoa.net');
1).使用'*'进行大小写的适配
SELECT * FROM Email WHERE Email ~* 'sqlGuide';
2).使用'!'进行反向的适配
SELECT * FROM Email WHERE Email !~ 'sqlguide';
3).使用'^'进行字串头、尾部适配
SELECT * FROM Email WHERE Email ~ '^d';
SELECT * FROM Email WHERE Email ~ 'edu$';
4).使用'\d'进行数字的适配
SELECT * FROM Email WHERE Email ~ '\d';
SELECT * FROM Email WHERE Email ~ '[0-9]';
SELECT * FROM Email WHERE Email ~ '\d+';
SELECT * FROM Email WHERE Email ~ '[0-9]+';
SELECT * FROM Email WHERE Email ~ '[0-9][0-9]';
SELECT * FROM Email WHERE Email ~ '\d{2,}';
5).匹配与替换
将首个出现数字替换为'*'
SELECT REGEXP_REPLACE(Email,'[0-9]','*') From Email;
将首个连续出现数字替换为'*'
SELECT REGEXP_REPLACE(Email,'[0-9]+','*') From Email;
将所有出现数字替换为'*'
SELECT REGEXP_REPLACE(Email,'[0-9]','*','g') From Email;
将非数字替换为空
SELECT REGEXP_REPLACE(Email,'\D','','g') FROM Email;
SELECT REGEXP_REPLACE(Email, '[^\w]+','','g') FROM Email;
这里的'g'就是模式匹配选项(Flag)。
Flag 'i': match case-insensitively.
Flag 'g': search globally for each occurrence.
6).返回邮件所在的域名集合
SELECT REGEXP_MATCHES(Email,'.+@(.*)$') FROM Email;
SELECT REGEXP_MATCHES(Email,'.+@(.*)\.(\w+)') as domain FROM Email;
SELECT REGEXP_MATCHES(Email,'.+@(.*)\.(\w+)') as domain FROM Email;
SELECT REGEXP_MATCHES(Email,'(\w+)@(\w+)\.(\w+)') as domain FROM Email;
SELECT (REGEXP_MATCHES(Email,'(\w+)@(\w+)\.(\w+)'))[3] as domaintld FROM Email;
7).取子串
SELECT DISTINCT SUBSTRING(Email FROM '.+@(.*)$') FROM Email;
SELECT SUBSTRING(Email FROM '.+@(\w+)\.(\w+)') FROM Email;
正则匹配返回可能是0或多个集合或行，在第13版本中测试还发现安全支持中文匹配。