模式匹配操作符
数据库提供了三种独立的实现模式匹配的方法：SQL LIKE操作符、SIMILAR TO操作符和POSIX-风格的正则表达式。除了这些基本的操作符外，还有一些函数可用于提取或替换匹配子串并在匹配位置分离一个串。
- LIKE
描述：判断字符串是否能匹配上LIKE后的模式字符串。如果字符串与提供的模式匹配，则LIKE表达式返回为真（NOT LIKE表达式返回假），否则返回为假（NOT LIKE表达式返回真）。
匹配规则：- 此操作符仅当模式匹配整个串时才能成功。若需匹配串内任意位置的字符序列，该模式必须以百分号开始和结束。
- 下划线 （_）代表（匹配）任何单个字符； 百分号（%）代表任意串的通配符。
- 要匹配文本里的下划线或者百分号，在提供的模式里相应字符必须前导逃逸字符。逃逸字符的作用是禁用元字符的特殊含义，缺省的逃逸字符是反斜线，也可以用ESCAPE子句指定一个不同的逃逸字符。
- 要匹配逃逸字符本身，需要输入两个逃逸字符。例如要写一个包含反斜线的模式常量，需要在SQL语句中输入两个反斜线。
- 参数standard_conforming_strings设置为off时，在文串常量中的任何反斜线需进行双写处理。因此，要在模式中匹配单个反斜线，SQL语句中需输入四个反斜线。（可通过使用ESCAPE选择不同的逃逸字符来避免这种情况，此操作将使反斜线不再是LIKE的特殊字符。但仍然是字符文本分析器的特殊字符，所以需要两个反斜线。）
- 在MYSQL模式下，可以通过写ESCAPE ''的方式不选择逃逸字符，此操作可以有效地禁用逃逸机制，但无法消除下划线和百分号在模式中的特殊含义。
- 在ORA模式下，参数standard_conforming_strings设置为on时，可以设置behavior_compat_options为ignore_like_default_escape，如果未显式指定ESCAPE '\'，则禁用反斜线（\）的特殊含义，匹配单个反斜线（\）字符。
- 该选项仅对关键字（LIKE、NOT LIKE、ILIKE、NOT ILIKE）生效，对操作符（~~、!~~、~~*、!~~*）不生效。
- 当模式串包含 ANY、SOME 或 ALL 子句时，此选项将不生效。
- 关键字ILIKE可以用于替换LIKE，区别是LIKE大小写敏感，ILIKE大小写不敏感。
- 操作符~~等效于LIKE，操作符~~*等效于ILIKE。
示例：
1 2 3 4 5
gaussdb=# SELECT 'abc' LIKE 'abc' AS RESULT; result ----------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' LIKE 'a%' AS RESULT; result ----------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' LIKE '_b_' AS RESULT; result ----------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' LIKE 'c' AS RESULT; result ----------- f (1 row)
- SIMILAR TO
描述：SIMILAR TO操作符根据自己的模式是否匹配给定串而返回真或者假。该操作符与LIKE语法相似，但采用SQL标准定义的正则表达式进行模式匹配。
匹配规则：- 和LIKE相同，此操作符仅当模式匹配整个串时才能成功。若需匹配串中任何位置的字符序列，模式需要以百分号作为起始符和终止符。
- 下划线 （_）代表（匹配）任何单个字符； 百分号（%）代表任意串的通配符。
- SIMILAR TO也支持以下从POSIX正则表达式借用的模式匹配元字符。
元字符
含义
|
表示选择（两个候选之一）。
*
表示重复前面的项零次或更多次。
+
表示重复前面的项一次或更多次。
?
表示重复前面的项零次或一次。
{m}
表示重复前面的项刚好m次。
{m,}
表示重复前面的项m次或更多次。
{m,n}
表示重复前面的项至少m次并且不超过n次。
()
把多个项组合成一个逻辑项。
[...]
与POSIX正则表达式相同，表示声明一个字符类。
- 前导逃逸字符可以禁用所有这些元字符的特殊含义。逃逸字符的使用规则和LIKE相同。
正则表达式函数：
支持使用函数substring(string from pattern for escape)截取匹配SQL正则表达式的子字符串。
示例：
1 2 3 4 5
gaussdb=# SELECT 'abc' SIMILAR TO 'abc' AS RESULT; result ----------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' SIMILAR TO 'a' AS RESULT; result ----------- f (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' SIMILAR TO '%(b|d)%' AS RESULT; result ----------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' SIMILAR TO '(b|c)%' AS RESULT; result ----------- f (1 row)
- POSIX正则表达式
描述：正则表达式是一个字符序列，定义一个串集合 （一个正则集）的缩写。 如果一个串是正则表达式描述的正则集中的一员时， 则说明这个串匹配该正则表达式。 POSIX正则表达式提供了比LIKE和SIMILAR TO操作符更强大的含义。表1列出了所有可用于POSIX正则表达式模式匹配的操作符。
表1 正则表达式匹配操作符 操作符
描述
例子
~
匹配正则表达式，大小写敏感
'thomas' ~ '.*thomas.*'
~*
匹配正则表达式，大小写不敏感
'thomas' ~* '.*Thomas.*'
!~
不匹配正则表达式，大小写敏感
'thomas' !~ '.*Thomas.*'
!~*
不匹配正则表达式，大小写不敏感
'thomas' !~* '.*vadim.*'
匹配规则：- 与LIKE不同，正则表达式允许匹配串里的任何位置，除非该正则表达式显式地挂接在串的开头或者结尾。
- 除了上文提到的元字符外， POSIX正则表达式还支持下列模式匹配元字符。
元字符
含义
^
表示串开头的匹配
$
表示串末尾的匹配
.
匹配任意单个字符
正则表达式函数：POSIX正则表达式支持以下函数。- substring(string from pattern)函数提供了抽取一个匹配POSIX正则表达式模式的子串的方法。
- regexp_count(string text, pattern text [, position int [, flags text]])函数提供了获取匹配POSIX正则表达式模式的子串数量的功能。
- regexp_instr(string text, pattern text [, position int [, occurrence int [, return_opt int [, flags text]]]])函数提供了获取匹配POSIX正则表达式模式子串位置的功能。
- regexp_substr(string text, pattern text [, position int [, occurrence int [, flags text]]])函数提供了抽取一个匹配POSIX正则表达式模式的子串的方法。
- regexp_replace(string, pattern, replacement [,flags ])函数提供了将匹配POSIX正则表达式模式的子串替换为新文本的功能。
- regexp_matches(string text, pattern text [, flags text])函数返回一个文本数组，该数组由匹配一个POSIX正则表达式模式得到的所有被捕获子串构成。
- regexp_split_to_table(string text, pattern text [, flags text])函数把一个POSIX正则表达式模式当作一个定界符来分离一个串。
- regexp_split_to_array(string text, pattern text [, flags text ])和regexp_split_to_table类似，是一个正则表达式分离函数，不过它的结果以一个text数组的形式返回。
正则表达式分离函数会忽略零长度的匹配，这种匹配发生在串的开头或结尾或者正好发生在前一个匹配之后。这和正则表达式匹配的严格定义是相悖的，后者由regexp_matches实现，但是通常前者是实际中最常用的行为。
示例：
1 2 3 4 5
gaussdb=# SELECT 'abc' ~ 'Abc' AS RESULT; result -------- f (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' ~* 'Abc' AS RESULT; result -------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' !~ 'Abc' AS RESULT; result -------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc'!~* 'Abc' AS RESULT; result -------- f (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' ~ '^a' AS RESULT; result -------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' ~ '(b|d)'AS RESULT; result -------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'abc' ~ '^(b|c)'AS RESULT; result -------- f (1 row)
虽然大部分的正则表达式搜索能够快速执行，但是仍可能被人为地处理成需要任意长的时间和任意量的内存。不建议从非安全模式来源接受正则表达式搜索模式，如果必须这样做，建议设置语句超时限制。使用SIMILAR TO模式的搜索具有同样的安全性危险， 因为SIMILAR TO提供了很多和POSIX-风格正则表达式相同的能力。LIKE搜索比其他两种选项简单得多，因此在接受非安全模式来源搜索时要更为安全。
- [NOT] REGEXP/ [NOT] RLIKE
描述：REGEXP操作符用于正则表达式匹配，符合POSIX正则匹配规则。根据自己的模式是否匹配给定字符串而返回真或者假。
下表描述了正则表达式操作符：
操作符名称
描述
语法说明
REGEXP
字符串是否与正则表达式匹配
expr REGEXP pat
RLIKE
字符串是否与正则表达式匹配（同REGEXP）
expr RLIKE pat
NOT REGEXP
字符串是否与正则表达式不匹配
expr NOT REGEXP pat
NOT RLIKE
字符串是否与正则表达式不匹配（同NOT REGEXP）
expr NOT RLIKE pat
匹配规则：- 正则表达式允许匹配字符串中的任何位置，除非该正则表达式显式地拼接在字符串的起始位置或结尾位置。
- REGEXP操作符支持的模式匹配元字符与POSIX正则表达式支持的模式匹配元字符一致。
- 操作符支持的转义字符匹配包括：
转义字符
描述
\b
退格键。
\f
换页符，如C语言。
\n
换行符，如C语言。
\r
回车符，如C语言。
\t
水平制表符，如C语言。
\uwxyz
（其中wxyz正好是四个十六进制数字）十六进制值为0xwxyz的字符。
\xhhh
（其中hhh是十六进制数字的任何序列）十六进制值为0xhhh的字符。
\xy
（其中xy正好是两个八进制数字）八进制值为0xy的字符。
\xyz
（其中xyz正好是三个八进制数字）八进制值为0xyz的字符。
- 模式字符串pat范围匹配：[a-dX]，[^a-dX]
[a-dX]表示匹配任何a、b、c、d或X的字符。[^a-dX]表示匹配不是a、b、c、d或X的字符。
两个字符之间的“-”构成一个范围，表示范围内所有字符匹配。要想包含文字字符“]”，该文字字符必须紧跟在第一个方括号“[”之后。要想包含文字字符“-”，它必须在第一个方括号“[”之后或在结束的方括号“]”之前。对于[]内未定义任何特殊含义的任何字符，与其本身匹配。
示例：
1 2 3 4 5
gaussdb=# SELECT 'abd' REGEXP 'a[bc]d' AS RESULT; result -------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'aed' REGEXP 'a[^bc]d' AS RESULT; result -------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'a-' REGEXP 'a[-b]' AS RESULT; result -------- t (1 row)
1 2 3 4 5
gaussdb=# SELECT 'aX]bc' REGEXP '^[]a-dXYZ]*$' AS RESULT; result -------- t (1 row)
- 模式字符串pat中[.characters.]匹配元素的字符序列。在括号表达式中（使用“[”和“]”），匹配用于校对元素的字符序列。字符为单个字符或诸如“space”等字符名。在文件regex/regc_locale中，可找到字符名称的完整列表。
1 2 3 4 5
gaussdb=# SELECT ' ' REGEXP '[[.space.]]' AS RESULT; result -------- t (1 row)
- 模式字符串pat中[=character_class=]字符匹配的字符类。在方括号表达式中编写，[=character_class=]代表等价类。它匹配具有相同排序校对值得所有字符，包括其本身。例如，如果o和(+)是等同类的成员，则[[=o=]]、[[=(+)=]]和[o(+)]是同义词。等同类不得用作范围的端点。
- 模式字符串pat中[:character_class:]字符匹配的字符类。在方括号表达式中编写（使用“[]”），[:character_class:]表示匹配属于该类的所有字符的字符类。特定区域可能会提供其他类名。字符类不得用作范围的端点。下表列出了标准类名。当涉及\字符时，需按说明配置相应的参数。
字符类名
描述
字符范围
alnum
文字数字字符
[0-9a-zA-Z]
alpha
文字字符
[a-zA-Z]
blank
空白字符
空白字符[\t]
cntrl
控制字符
[\x01-\x1F]
digit
数字字符
[0-9]
graph
图形字符
[^\x01-\x20]
lower
小写文字字符
[a-z]
print
图形字符
[^\x01-\x20]
punct
标点字符
[-!"#$%&'( )*+,./:;<=>?@[\\]^_`{|}~]
space
空格、制表符、新行、回车
[\n\r\t\x0B]
upper
大写文字字符
[A-Z]
xdigit
十六进制数字字符
[0-9a-fA-F]
示例：
1 2 3 4 5
gaussdb=# SELECT '\n' REGEXP '[[:space:]]' AS RESULT; result -------- t (1 row)
- 模式字符串pat中[[:<:]], [[:>:]]匹配字符串的开始和结束位置。
1 2 3 4 5
gaussdb=# SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]' AS RESULT; result -------- t (1 row)
- 匹配特殊字符的文字实例，需要在特殊字符前加入两个反斜线，包括(、)、"、^、+、? 特殊字符。特别地，对与单引号的匹配，需要在原字符串写'\''，与模式字符串中'\\\''匹配。当涉及\字符时，需按说明配置相应的参数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
gaussdb=# SELECT 'a+b' REGEXP 'a\\+b' AS RESULT; result -------- t (1 row) gaussdb=# SELECT '\'' REGEXP '\\\'' AS RESULT; result -------- t (1 row) gaussdb=# SELECT '\\' REGEXP '\\\\' AS RESULT; result -------- t (1 row)
- 当设置了区分大小写的字符集字符序时，正则匹配也会区分大小写。
1 2 3 4 5
gaussdb=# SELECT 'abc' REGEXP 'ABC' COLLATE utf8mb4_bin AS RESULT; result -------- f (1 row)
- 入参Expr或pat为NULL时，返回空值。
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT NULL REGEXP '*' AS RESULT; result -------- (1 row) gaussdb=# SELECT '-' REGEXP NULL AS RESULT; result -------- (1 row)
- 此操作符仅在MYSQL模式数据库下支持，且仅在sql_compatibility='MYSQL'有效，设置参数b_format_version='5.7'后，REGEXP操作符与~*操作符等效，NOT REGEXP操作符与!~*操作符等效。
- 当设置b_format_dev_version为s2时，默认standard_conforming_strings=off且escape_string_warning=off，此时在文串常量中输入的任何反斜线都需要被重复输入。因此，写一个匹配单个反斜线的模式，实际上要在语句中输入四个反斜线。
- 此操作符仅支持字符串类型、文本类型、以及可以隐式转换成字符串类型和文本类型的其他数据类型，不支持bytea类型。
