Gaussdb模式匹配操作符详细介绍
gaussdb数据库目前提供了三种独立的实现模式匹配的方法，分别为LIKE操作符、SIMILAR TO操作符和POSIX-风格的正则表达式 。除了这些基本的操作符外，还有一些函数可用于提取或替换匹配子串并在匹配位置分离一个串。
1、like
like应该是我们经常使用的方式，判断字符串是否能匹配上LIKE后的模式字符串，如果匹配，则LIKE表达式返回为真（NOT LIKE表达式返回假），否则返回为假（NOT LIKE表达式返回真）。如果不添加%或_，即全部字符串匹配相当于=；如果要匹配在字符串内的任何位置，该模式必须以%开头和结尾。
| 规则 | 说明 |
|---|---|
| % | 任意字符串的通配符 |
| _ | （匹配）任何单个字符 |
| \\ 、%、\_ | 转义字符，查询的内容中需要匹配%、_、\，需要在前面添加\ |
| like | 大小写敏感即区分大小写 |
| ilike | 大小写不敏感即不区分大小写 |
通配符
openGauss=# SELECT 'abc' LIKE 'abc' AS RESULT;
result
--------
t
(1 row)
openGauss=# SELECT 'abc' LIKE 'a%' AS RESULT;
result
--------
t
(1 row)
openGauss=# SELECT 'abc' LIKE '%b%' AS RESULT;
result
--------
t
(1 row)
openGauss=# SELECT 'abc' LIKE '_b_' AS RESULT;
result
--------
t
(1 row)
openGauss=# SELECT 'abc' LIKE 'c' AS RESULT;
result
--------
f
(1 row)
是否区分大小写
openGauss=# select 'abc' like 'A%' as result;
result
--------
f
(1 row)
openGauss=# select 'abc' ilike 'A%' as result;
result
--------
t
(1 row)
ESCAPE子句指定其他逃逸字符
下面案例中的模式字符串’abc %%'，第一个%由于自定义了逃逸字符 ，因此$%作为一个普通字符存在，只需要匹配查询的内容中包含有%；最后一个%则是通配符作用
openGauss=# with tmp as (select 'abc%def' as result union all select 'abcdef' as result)
openGauss-# select * from tmp where result like 'abc$%%' escape '$';
result
---------
abc%def
(1 row)
2、SIMILAR TO
SIMILAR TO的用法和LIK非常类似，其结果也是根据匹配结果返回真假。另外比较特殊的就是支持使用SQL标准定义的正则表达式理解模式，支持的规则除了%和_之外，也支持下面这些从POSIX正则表达式借用的模式匹配元字符。
| 元字符 | 含义 |
|---|---|
| | | 表示选择（两个候选之一） |
| * | 表示重复前面的项零次或更多次，可以理解为任意次 |
| + | 表示重复前面的项一次或更多次 |
| ? | 表示重复前面的项零次或一次 |
| {m} | 表示重复前面的项刚好m次。 |
| {m,} | 表示重复前面的项m次或更多次 |
| {m,n} | 表示重复前面的项至少m次并且不超过n次 |
| () | 把多个项组合成一个逻辑项，就相当于把整个()内的内容作为匹配项 |
| […] | 声明一个字符类，就像POSIX正则表达式一样 |
针对上述描述重复的次数，是指元字符修饰的字符或者字符组重复多少次，不要理解为该字符或字符组可以和源字符串可以匹配多少次
常见元字符使用
--|元字符
openGauss=# SELECT 'abc' SIMILAR TO '%(af|bc)%' AS RESULT;
result
--------
t
(1 row)
--*元字符
--当前的*时，即使没有可匹配的内容，也返回的是true
openGauss=# SELECT 'abc' SIMILAR TO '%(o)*%' AS RESULT;
result
--------
t
(1 row)
openGauss=# SELECT 'abcdefosp' SIMILAR TO '%(o)*%' AS RESULT;
result
--------
t
(1 row)
--+元字符，该示例由于一次也未匹配到，所以返回false
openGauss=# SELECT 'abc' SIMILAR TO '%(o)+%' AS RESULT;
result
--------
f
(1 row)
--+元字符，该示例匹配到多次o，所以返回true
openGauss=# SELECT 'abcdefooosp' SIMILAR TO '%(o)+%' AS RESULT;
result
--------
t
(1 row)
--?元字符，，该示例未匹配，返回结果也是true
openGauss=# SELECT 'abc' SIMILAR TO '%(o)?%' AS RESULT;
result
--------
t
(1 row)
--{m,n}元字符，对于字符组oo，需要至少匹配2次，即为oooo，可知原字符串不符合结果，返回结果为false。
openGauss=# SELECT 'abcoodefoooosp' SIMILAR TO '%(oo){2,3}%' AS RESULT;
result
--------
f
(1 row)
SELECT 'abcoodefoooosp' SIMILAR TO '%(oo){2,3}%' AS RESULT;
3、POSIX正则表达式
正则表达式是一个字符序列，它是定义一个串集合（一个正则集）的缩写。 如果一个串是正则表达式描述的正则集中的一员时， 我们就说这个串匹配该正则表达式。 POSIX正则表达式提供了比LIKE和SIMILAR TO操作符更强大的含义。列出了所有可用于POSIX正则表达式模式匹配的操作符。
正则表达式匹配操作符
| 操作符 | 描述 | 例子 |
|---|---|---|
| ~ | 匹配正则表达式，大小写敏感，等价与like | ‘thomas’ ~ ‘.thomas.’ |
| ~* | 匹配正则表达式，大小写不敏感，等价与ilike | ‘thomas’ ~* ‘.Thomas.’ |
| ！~ | 不匹配正则表达式，大小写敏感，等价与not like | ‘thomas’ !~ ‘.Thomas.’ |
| ！~* | 不匹配正则表达式，大小写不敏感，等价与not ilike | ‘thomas’ !~* ‘.vadim.’ |
--匹配正则表达式，大小写敏感
openGauss=# SELECT 'abc' ~ 'Abc' AS RESULT;
result
--------
f
(1 row)
--匹配正则表达式，大小写不敏感
openGauss=# SELECT 'abc' ~* 'Abc' AS RESULT;
result
--------
t
(1 row)
--不匹配正则表达式，大小写敏感
openGauss=# SELECT 'abc' !~ 'Abc' AS RESULT;
result
--------
t
(1 row)
--不匹配正则表达式，大小写不敏感
openGauss=# SELECT 'abc'!~* 'Abc' AS RESULT;
result
--------
f
(1 row)
匹配规则
与LIKE不同，正则表达式允许匹配串里的任何位置，除非该正则表达式显式地挂接在串的开头或者结尾。
除了上文提到的元字符外， POSIX正则表达式还支持下列模式匹配元字符。
| 元字符 | 含义 |
|---|---|
| \ | 转义字符 |
| ^ | 表示串开头的匹配 |
| $ | 表示串末尾的匹配 |
| . | 匹配任意单个字符 |
| (abc) | 把多个字符组合成一个匹配原子，就相当于把整个()内的内容作为匹配项 |
| [abc] | 匹配abc中的任何一个字符，内部可以嵌套(abc)，然后这个做一个原子，比如：[abc(de)] |
| [^abc] | 负值字符集合。匹配未包含的任意字符。例如，[^abc]可以匹配“plain”中的“plin”任一字符。 |
| | | 表示选择（两个候选之一）例如： a|b |
| \d | 匹配任何数字，就像 [[:digit:]] |
| \s | 匹配任何空白字符，就像 [[:space:]] ,包括空格、制表符和换行 |
| \w | 匹配任何单词字符，就像 [[:word:]] |
| \D | 匹配任何非数字，就像 [^[:digit:]] |
| \S | 匹配任何非空白字符，就像 [^[:space:]] |
| \W | 匹配任何非单词字符，就像 [^[:word:]] |
| \A | 只在串开头匹配（与^的不同） |
| \m | 只在一个词的开头匹配 |
| \M | 只在一个词的末尾匹配 |
| \y | 只在一个词的开头或末尾匹配 |
| \Y | 只在一个词的不是开头或末尾的点上匹配 |
| \Z | 只在串的末尾匹配（与$的不同） |
| \r | 回车 |
常见元字符使用
--()匹配规则
openGauss=# select 'abc' ~ '(ab|ac)' as result;
result
--------
t
(1 row)
--[]匹配规则，其中的任何一个字符匹配到源字符串就返回true
openGauss=# select 'abc' ~ '[bef]' as result;
result
--------
t
(1 row)
--[^abc]，只要源字符串含有非限定范围的字符，就返回true
openGauss=# select 'china' ~ '[^abc]' as result;
result
--------
t
(1 row)
--匹配空白字符
openGauss=# select 'china ' ~ '\s' as result;
result
--------
t
(1 row)
匹配两个词组查询场景
下面的案例主要就是查询数据库会话中使用is not null或者is null语法的场景
--如果两个词组中间只包含空格，可以直接添加\s，另外还需要考虑有多个空白的时候，因此也需要添加量词来修饰，可以添加+
openGauss=# select 'select * from t1 where is not null' ~ '(is)\s+(not)' as result;
result
--------
t
(1 row)
--如果两个词组中间包含不固定的字符且至少有一个字符，可以添加 .+来指定任意字符；
openGauss=# select 'select * from t1 where is not null' ~ '(is).*(null)' as result;
result
--------
t
(1 row)
--如果两个词组中间，可能存在not字符串且不存在其他字符串，可以适用((not)?\s+)修饰not字符串的匹配规则，外层的括号可以不需要。
--外层的括号只是为了方便区分匹配的分组
openGauss=# select 'select * from t1 where is not null' ~ '(is)\s+((not)?\s+)(null)' as result;
result
--------
t
(1 row)
--去掉括号后的结果和之前是一致的
openGauss=# select 'select * from t1 where is not null' ~ '(is)\s+(not)?\s+(null)' as result;
result
--------
t
(1 row)
匹配规则，是否需要考虑换行
匹配多个词组的时候，目前验证，对于空格、制表符、换行，都按照匹配任何空白字符处理即可。
create table t1 (id int,context varchar(1000));
insert into t1 values(1,'select * from t1
where is
not null');
insert into t1 values(2,'select * from t1 where is
null');
insert into t1 values(3,'select * from t1 where 1=2');
insert into t1 values(4,'not else');
insert into t1 values(5,'ifnot else');
insert into t1 values(6,'notrr else');
insert into t1 values(7,'select * from t1 where is not null');
--查询
openGauss=> select * from t1 where regexp_like(context,'(is)\s+(not)?\s?(null)','i');
id | context
----+------------------------------------
1 | select * from t1 \r +
| where is \r +
| not null
2 | select * from t1 where is \r +
| null
7 | select * from t1 where is not null
(3 rows)
--插入
insert into t1 values(8,'where group
by ');
insert into t1 values(9,'where group by ');
--匹配换行\r
openGauss=> select * from t1 where regexp_like(context,'(group)\r+','i');
id | context
----+---------------
8 | where group\r+
| by
(1 row)
--匹配任何空白字符\s，根据结果可知，\s是包含换行信息的
openGauss=> select * from t1 where regexp_like(context,'(group)\s+','i');
id | context
----+-----------------
8 | where group\r +
| by
9 | where group by
(2 rows)
按照词组匹配，精确查找
我们在查询匹配关键字的时候，一般需要精确查找匹配，而不需要那些包含有查询关键字的数据行
--通过正则表达式约束\m和\M来限定一个词的开头和结尾匹配
openGauss=> select * from t1 where regexp_like(context,'\mnot\M','i');
id | context
----+------------------------------------
1 | select * from t1 \r +
| where is \r +
| not null
4 | not else
7 | select * from t1 where is not null
(3 rows)
--\m一个词的开头匹配
openGauss=> select * from t1 where regexp_like(context,'\mnot','i');
id | context
----+------------------------------------
1 | select * from t1 \r +
| where is \r +
| not null
4 | not else
6 | notrr else
7 | select * from t1 where is not null
(4 rows)
--\M一个词的末尾匹配
openGauss=> select * from t1 where regexp_like(context,'not\M','i');
id | context
----+------------------------------------
1 | select * from t1 \r +
| where is \r +
| not null
4 | not else
5 | ifnot else
7 | select * from t1 where is not null
(4 rows)
?和*量词的区别
在查询匹配场景下，我个人理解应该是一致的，但是对于一些替换场景则是有区别的
--?是匹配0或1次，所以(o)?b匹配的子串为ob，替换为了foearbaz
openGauss=> SELECT regexp_replace('foobarobaz', '(o)?b', 'e');
regexp_replace
----------------
foearobaz
(1 row)
--*是匹配0或更多次，所以(o)?b匹配的子串为oob，替换为了e，结果为fearbaz
openGauss=> SELECT regexp_replace('foobarobaz', '(o)*b', 'e');
regexp_replace
----------------
fearobaz
(1 row)
--对于flat参数，i表示进行大小写无关的匹配，g表示替换每一个匹配的子字符串而不仅仅是第一个
openGauss=> SELECT regexp_replace('foobarobaz', '(o)*b', 'e','g');
regexp_replace
----------------
feareaz
(1 row)
正则表达式支持的函数
对于具体的正则函数的使用，本文不做详细介绍，后续再写文章详细介绍。正则表达式支持的函数如下，
substring(string from pattern)
描述：截取匹配POSIX正则表达式的子字符串。如果没有匹配它返回空值，否则返回文本中匹配模式的那部分。
regexp_count(string text, pattern text [, position int [, flags text]])
描述：获取满足匹配的子串个数
regexp_instr(string text, pattern text [, position int [, occurrence int [, return_opt int [, flags text]]]])
描述：获取满足匹配条件的子串位置（从1开始）。如果没有匹配的子串，则返回0。
regexp_substr(string text, pattern text [, position int [, occurrence int [, flags text]]])
描述：正则表达式的抽取子串函数。与substr功能相似，正则表达式出现多个并列的括号时，也全部处理
regexp_replace(string, pattern, replacement [,flags ])
描述：替换匹配POSIX正则表达式的子字符串。如果没有匹配pattern，那么返回不加修改的string串。如果有匹配，则返回的string串里面的匹配子串将被replacement串替换掉。
replacement串可以包含\n，其中\n是1到9，表明string串里匹配模式里第n个圆括号子表达式的子串应该被插入，并且它可以包含\&表示应该插入匹配整个模式的子串。
可选的flags参数包含零个或多个改变函数行为的单字母标记。flags 支持的选项值及含义描述如表1 flags 支持的选项值所示。返回值类型：varchar
regexp_matches(string text, pattern text [, flags text])
描述：返回string中所有匹配POSIX正则表达式的子字符串。如果pattern不匹配，该函数不返回行。如果模式不包含圆括号子表达式，则每一个被返回的行都是一个单一元素的文本数组，其中包括匹配整个模式的子串。如果模式包含圆括号子表达式，该函数返回一个文本数组，它的第n个元素是匹配模式的第n个圆括号子表达式的子串。
flags参数为可选参数，包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配，g表示替换每一个匹配的子字符串而不仅仅是第一个。
regexp_split_to_array(string text, pattern text [, flags text ])
描述：用POSIX正则表达式作为分隔符，分隔string。和regexp_split_to_table相同，不过regexp_split_to_array会把它的结果以一个text数组的形式返回。返回值类型：text[]
regexp_split_to_table(string text, pattern text [, flags text])
描述：用POSIX正则表达式作为分隔符，分隔string。如果没有与pattern的匹配，该函数返回string。如果有至少有一个匹配，对每一个匹配它都返回从上一个匹配的末尾（或者串的开头）到这次匹配开头之间的文本。当没有更多匹配时，它返回从上一次匹配的末尾到串末尾之间的文本。
flags参数包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配。返回值类型：setof text
regexp_like(text,text,text)
描述：正则表达式的模式匹配函数。返回值类型：bool
总结
本文中整理的正则匹配规则为比较常见的规则，相对来说上手还是比较容易的，这些规则对于我们的日常运维开发应该是足够使用的。另外这三种模式匹配，其实使用场景也是稍微有所不同。
1、like常用于简单的模糊匹配查询
2、SIMILAR TO语法可以支持正则模式匹配，适用于大部分场景
3、正则表达式相比SIMILAR TO，支持一些正则函数的使用并且支持一些其他特殊的匹配规则
总之，正则表达式是一种强大的工具，它在文本处理领域具有广泛的应用，并且具有灵活性、通用性和高效性等优点。通过掌握正则表达式，开发或运维人员能够更高效地处理文本数据。
上述的整理，是通过查看gauss的官网文档并结合PG官网资料整理常用的正则表达式。如果个别内容有误，望各位大佬多多指正。
发现的问题
1、Gaussdb文档没有关于正则表达式匹配规则的详细介绍，希望后续可以完善该部分文档
2、POSIX正则表达式支持的函数中描述中缺少了regexp_like，但是在regexp_like函数详细介绍页是有该函数支持正则表达式的描述
模式匹配，其实使用场景也是稍微有所不同。
1、like常用于简单的模糊匹配查询
2、SIMILAR TO语法可以支持正则模式匹配，适用于大部分场景
3、正则表达式相比SIMILAR TO，支持一些正则函数的使用并且支持一些其他特殊的匹配规则
总之，正则表达式是一种强大的工具，它在文本处理领域具有广泛的应用，并且具有灵活性、通用性和高效性等优点。通过掌握正则表达式，开发或运维人员能够更高效地处理文本数据。
上述的整理，是通过查看gauss的官网文档并结合PG官网资料整理常用的正则表达式。如果个别内容有误，望各位大佬多多指正。
发现的问题
1、Gaussdb文档没有关于正则表达式匹配规则的详细介绍，希望后续可以完善该部分文档
2、POSIX正则表达式支持的函数中描述中缺少了regexp_like，但是在regexp_like函数详细介绍页是有该函数支持正则表达式的描述
419
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
