模式匹配操作符

数据库提供了[NOT] LIKE操作符和正则表达式模式匹配。

[NOT] LIKE

描述：判断字符串是否能匹配上LIKE后的模式字符串。如果字符串与提供的模式匹配，则LIKE表达式返回为真（NOT LIKE表达式返回假），否则返回为假（NOT LIKE表达式返回真）。支持含有0字符的操作数（字符串类型、二进制类型、位串类型）进行模式匹配操作。

匹配规则：

此操作符只有在它的模式匹配整个串的时候才能成功。如果要匹配在串内任何位置的序列，该模式必须以百分号开头和结尾。
下划线 _代表匹配任何单个字符；百分号%代表任意串的通配符。
要匹配文本里的下划线或者百分号，在提供的模式里相应字符必须前导逃逸字符。逃逸字符的作用是禁用元字符的特殊含义，缺省的逃逸字符是反斜线，也可以用ESCAPE子句指定一个不同的逃逸字符。
要匹配逃逸字符本身，写两个逃逸字符。例如要写一个包含反斜线的模式常量，需要在SQL语句里写两个反斜线。
是否区分大小写，与字符序有关，比如，默认的utf8mb4_general_ci不区分大小写。
操作符~~等效于LIKE，!~~等效于NOT LIKE。

示例：

一般字符串匹配，字符序为utf8mb4_general_ci，不区分大小写。

m_db=# INSERT INTO t VALUES ('ab^CD&eF','ab^CD&eF','ab^CD&eF','ab^CD&eF','ab^CD&eF','ab^CD&eF');
m_db=# SELECT c FROM t WHERE c LIKE '%B^c%';
    c     
----------
 ab^CD&eF
(1 row)

BINARY/BLOB相关字段，字符序为utf8mb4_bin，区分大小写。

m_db=# INSERT INTO t VALUES (b'0110001', 'ab^CD&eF', 'ab^CD&eF');
m_db=# SELECT b FROM t WHERE b LIKE 'ab%';
     b
------------
 ab^CD&eF
(1 row)

用ESCAPE字符匹配_及%。

m_db=# INSERT INTO t VALUES ('ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF');
m_db=# SELECT c FROM t WHERE c LIKE '%B\_c%';
    c
----------
 ab_CD%eF
(1 row)

指定ESCAPE字符。

m_db=# INSERT INTO t VALUES ('ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF');
m_db=# SELECT c FROM t WHERE c LIKE '%B^_c%' ESCAPE '^';
    c
----------
 ab_CD%eF
(1 row)

[NOT] REGEXP

描述：REGEXP操作符用于正则表达式匹配。根据自己的模式是否匹配给定字符串而返回真或者假。下表描述了正则表达式操作符：

操作符名称	描述	语法说明
REGEXP	字符串是否与正则表达式匹配。	expr REGEXP pat
NOT REGEXP	字符串是否与正则表达式不匹配，REGEXP的否定。	expr NOT REGEXP pat

正则表达式语法：

^：匹配字符串的开头。
$：匹配字符串的末尾。
.：匹配任意字符（包括回车和换行符）。
a*：匹配零个或多个“a”字符。
a+：匹配一个或多个“a”字符。
a?：匹配零个或一个“a”字符。
de | abc：匹配“de”字符或“abc”字符。
(abc)*：匹配零个或多个“abc”字符。
a{1}，a{2,3}：a{1}表示重复“a”字符一次；a{2,3}表示重复“a”2~3次。
[a-dX]，[^a-dX]：范围匹配。[a-dX]匹配a、b、c、d或X的字符。[^a-dX]表示匹配不是a、b、c、d或X的字符。

[.characters.]：匹配用于校对元素的字符序列。允许的字符名称以及匹配的字符如下表所示。

Name	Character	Name	Character
NUL	0	SOH	1
STX	2	ETX	3
EOT	4	ENQ	5
ACK	6	BEL	7
alert	7	BS	10
backspace	\b'	HT	11
tab	\t'	LF	12
newline	\n'	VT	13
vertical-tab	\v'	FF	14
form-feed	\f'	CR	15
carriage-return	\r'	SO	16
SI	17	DLE	20
DC1	21	DC2	22
DC3	23	DC4	24
NAK	25	SYN	26
ETB	27	CAN	30
EM	31	SUB	32
ESC	33	IS4	34
FS	34	IS3	35
GS	35	IS2	36
RS	36	IS1	37
US	37	space	'
exclamation-mark	!'	quotation-mark	"'
number-sign	#'	dollar-sign	$'
percent-sign	%'	ampersand	&'
apostrophe	\''	left-parenthesis	('
right-parenthesis	)'	asterisk	*'
plus-sign	+'	comma	,'
hyphen	-'	hyphen-minus	-'
period	.'	full-stop	.'
slash	/'	solidus	/'
zero	0'	one	1'
two	2'	three	3'
four	4'	five	5'
six	6'	seven	7'
eight	8'	nine	9'
colon	:'	semicolon	;'
less-than-sign	<'	equals-sign	='
greater-than-sign	>'	question-mark	?'
commercial-at	@'	left-square-bracket	['
backslash	\\'	reverse-solidus	\\'
right-square-bracket	]'	circumflex	^'
circumflex-accent	^'	underscore	_'
low-line	_'	grave-accent	`'
left-brace	{'	left-curly-bracket	{'
vertical-line	\|'	right-brace	}'
right-curly-bracket	}'	tilde	~'
DEL	177	-	-

[=character_class=]：表示等价类。匹配具有相同排序校对值的所有字符，包括其本身。例如，如果o和(+)是等同类的成员，那么[[=o=]]、[[=(+)=]]和[o(+)]是同义词。

[: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]

[[:<:]]，[[:>:]]：分别匹配字符串的开始和结束。

示例：

m_db=# SELECT 'fo\nfo' REGEXP '^fo$'; 
 ?column? 
----------
 f
(1 row)

m_db=# SELECT 'fofo' REGEXP '^fo';
 ?column? 
----------
 t
(1 row)

m_db=# SELECT 'Ban' REGEXP '^Ba*n';  
 ?column? 
----------
 t
(1 row)

m_db=# SELECT 'Bn' REGEXP '^Ba?n';
 ?column? 
----------
 t
(1 row)

m_db=# SELECT 'pi' REGEXP 'pi|apa';
 ?column? 
----------
 t
(1 row)

m_db=# SELECT 'aXbc' NOT REGEXP '^[a-dXYZ]$';
 ?column? 
----------
 t
(1 row)

当m_format_behavior_compat_options开启enable_escape_string配置开关，支持的转义字符匹配包括：

转义字符	描述
\'	一个单引号字符。
\"	一个双引号字符。
\b	一个退格符。
\n	一个换行符。
\r	一个回车符。
\t	一个制表符。
\Z	ASCII 26（Control Z）。
\\	一个反斜杠符。
\%	一个%符。
\_	一个_符。
\0	GaussDB报错：invalid byte sequence for encoding "UTF8": 0x00。

当m_format_behavior_compat_options关闭enable_escape_string配置开关，并设置standard_conforming_strings='off', escape_string_warning='off', backslash_quote='on'，支持的转义字符匹配包括：

转义字符	描述
\'	一个单引号字符。
\b	退格键。
\f	换页符，如C语言。
\n	换行符，如C语言。
\r	回车符，如C语言。
\t	水平制表符，如C语言
\uwxyz	（其中wxyz 是四个十六进制数字）十六进制值为0xwxyz的字符。
\xhhh	（其中hhh是十六进制数字的任何序列）十六进制值为0xhhh的字符。
\0	GaussDB报错：invalid byte sequence for encoding "UTF8": 0x00。
\xy	（其中xy是两个八进制数字）八进制值为0xy的字符。
\xyz	（其中xyz是三个八进制数字）八进制值为0xyz的字符。