南大通用GBase 8a 模糊查询和正则函数regexp_replace、regexp_like

本文介绍在GBase 8a数据库集群里,和正则有关的功能和函数,包括like、REGEXP、RLIKE关键字,以及regexp_like、regexp_replace、regexp_instr、regexp_substr等函数。

大小写问题

经过测试

  • 对于字符串常量,比如‘ABCD', like和rlike都是不区分大小写的;
  • 对于Express表的字段,是区分大小写的。

regexp_like 默认区分大小写。

字符串常量

gbase> set @str='aBc';  select @str like '%b%', @str rlike 'b',regexp_like(@str,'b');
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

+-----------------+----------------+-----------------------+
| @str like '%b%' | @str rlike 'b' | regexp_like(@str,'b') |
+-----------------+----------------+-----------------------+
|               1 |              1 |                     0 |
+-----------------+----------------+-----------------------+
1 row in set (Elapsed: 00:00:00.00)


字段

gbase> select * from test1_n1 where address like '%d%';
+------+----------------+-------------+--------------------------------------------+
| id   | name           | address     | note                                       |
+------+----------------+-------------+--------------------------------------------+
|    1 | 123445         | address1    | 123445123445123445                         |
|    2 | 123445645656   | address2    | 123445645656123445645656123445645656       |
|    3 | 12344564565633 | ad212dress2 | 123445645656331234456456563312344564565633 |
+------+----------------+-------------+--------------------------------------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> select * from test1_n1 where address like '%D%';
Empty set (Elapsed: 00:00:00.00)

gbase> select * from test1_n1 where address rlike 'd';
+------+----------------+-------------+--------------------------------------------+
| id   | name           | address     | note                                       |
+------+----------------+-------------+--------------------------------------------+
|    1 | 123445         | address1    | 123445123445123445                         |
|    2 | 123445645656   | address2    | 123445645656123445645656123445645656       |
|    3 | 12344564565633 | ad212dress2 | 123445645656331234456456563312344564565633 |
+------+----------------+-------------+--------------------------------------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> select * from test1_n1 where address rlike 'D';
+------+----------------+-------------+--------------------------------------------+
| id   | name           | address     | note                                       |
+------+----------------+-------------+--------------------------------------------+
|    1 | 123445         | address1    | 123445123445123445                         |
|    2 | 123445645656   | address2    | 123445645656123445645656123445645656       |
|    3 | 12344564565633 | ad212dress2 | 123445645656331234456456563312344564565633 |
+------+----------------+-------------+--------------------------------------------+
3 rows in set (Elapsed: 00:00:00.00)

如果要like区分,可以增加binary关键字,比如

gbase> set @str='aBc';  select @str like binary '%b%', @str rlike binary 'b',regexp_like(@str,'b');
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

+------------------------+-----------------------+-----------------------+
| @str like binary '%b%' | @str rlike binary 'b' | regexp_like(@str,'b') |
+------------------------+-----------------------+-----------------------+
|                      0 |                     0 |                     0 |
+------------------------+-----------------------+-----------------------+
1 row in set (Elapsed: 00:00:00.00)

like

最常用的使用%和下划线_进行模糊匹配的关键字。

语法

expr LIKE pat [ESCAPE 'escape-char']

说明

使用 SQL 的简单的正则表达式进行比较的模式匹配。如果表达式 expr 匹配 pat,返回 1(TRUE),否则返回 0(FALSE)。
模式未必就是文字字符串,例如,它可以使用字符串表达式或表列。可以在模式中使用下面所示的两个通配符与 LIKE 配合使用。

% 匹配任意多个字符,或零个字符。
_ 严格地匹配一个字符。

一个like里可以用多个匹配符。

\% 匹配%
\_ 匹配下划线_

转义符默认是右斜杠\,可以用ESCAPE参数自定义。

注意:由于 GBase 8a MPP Cluster 在字符串中使用 C 转义语法(例如,用“\n”代表一个换行字符),在 LIKE 字符串中,必须将用到的“\”双写;
例如,若要查找“\n”,必须将其写成“\n”。而若要查找“\”,则必须将其写成‘\\’。原因是反斜线符号会被语法分析程序剥离一次,在进行模式匹配时,又会被剥离一次,最后会剩下一个反斜线符号接受匹配。

样例

正常匹配

gbase> select 'abcdefg' like '%b_d%g';
+-------------------------+
| 'abcdefg' like '%b_d%g' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | First  |
|    2 | Second |
+------+--------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> select * from t1 where name like 'F_r%t';
+------+-------+
| id   | name  |
+------+-------+
|    1 | First |
+------+-------+
1 row in set (Elapsed: 00:00:00.01)

转义符匹配%和_

通过转义符进行匹配。

gbase> select * from t1 where name like 'F%\_%';
+------+-------+
| id   | name  |
+------+-------+
|    3 | Fo_ur |
+------+-------+
1 row in set (Elapsed: 00:00:00.01)

指定转义符

如下指定|作为转义符。

gbase> select * from t1 where name like 'F%|_%' escape '|';
+------+-------+
| id   | name  |
+------+-------+
|    3 | Fo_ur |
+------+-------+
1 row in set (Elapsed: 00:00:00.01)

REGEXP、RLIKE

正则匹配的关键字。

语法

expr REGEXP pat, expr RLIKE pat

说明

其中的pat是正则表达式。注意默认不区分大小写。可以用regexp_like函数执行匹配原则。

样例

普通匹配

匹配f开头,后面任意2个字符,st结尾的。 注意F和f没有区分大小写。

gbase> select 'First' regexp 'f.{2}st';
+--------------------------+
| 'First' regexp 'f.{2}st' |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select 'First' regexp 'f.{3}st';
+--------------------------+
| 'First' regexp 'f.{3}st' |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (Elapsed: 00:00:00.00)

匹配136-139的手机号

gbase> select '13812345678' regexp '13[6-9][0-9]{8}';
+----------------------------------------+
| '13812345678' regexp '13[6-9][0-9]{8}' |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

regexp_like()

语法

REGEXP_LIKE(source_char, pattern [, match_parameter]) 

说明

模糊匹配指定的字符串。

source_char 原始字符串或者字段。
pattern 正则表达式
match_parameter 匹配行为参数。

可通过设置该参数改变默认的匹配功能行为。可以是列名,列的内容不能超出该参数的值域范围。使用时该参数用单引号包围,例如:’i’。

默认和设置成 NULL 情况下“.”不匹配换行符,源字符串被看作一行。参数可选项如下:
i:大小写不敏感;
c:大小写敏感;
n:点号(.)匹配换行符号;
m:多行模式;
x:扩展模式,忽略正则表达式中的空白字符。
用户同时指定多个互斥参数(i,c)可选项时,系统按照最后出现的参数处理。

默认大小写敏感。

样例

普通匹配

注意F是大小写敏感匹配的。

gbase> select regexp_like('First','F.{2}st');
+--------------------------------+
| regexp_like('First','F.{2}st') |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select regexp_like('First','f.{2}st');
+--------------------------------+
| regexp_like('First','f.{2}st') |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

强制大小写不敏感

gbase> select regexp_like('First','f.{2}st','i');
+------------------------------------+
| regexp_like('First','f.{2}st','i') |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

匹配换行符

gbase> select regexp_like('Fir\nst','f.{3}st','in');
+---------------------------------------+
| regexp_like('Fir\nst','f.{3}st','in') |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

regexp_replace()

正则替换函数

语法

regexp_replace(source_char,pattern[,replace_string[,position[,occurren
ce[match_option]]]])

说明

用 replace_string 指定的字符串替换源字符串中与 pattern 指定的正则表达式相匹配的字符串。

source_char
源字符串。该参数支持的数据类型与 8a 的 replace 函数的 src 参数一致。

pattern
正则表达式。每个正则表达式最多可包含 512 个字节。具体语法规则请参考 PCRE-7.8 版本的语法规则说明

replace_string
替换字符串。替换字符串可以包含反向引用的数字表达式(\n,n 的取值范围是[1,9])

position
开始匹配的位置,如果不指定默认为 1,即从 source_char 的第一个字符开始匹配。position 为一个正整数。

occurrence
正则匹配的序数。是一个非负的整数,默认值为 0。
指定为 0,则替换所有匹配到的字符串;
如果指定为整数 n,则替换第 n 次匹配到的字符串;

match_parameter
可通过设置该参数改变默认的匹配功能行为。默认情况下“.”不匹配换行符,源字符串被看作一行。参数可选项如下:
i:大小写不敏感;
c:大小写敏感;
n:点号(.)不匹配换行符号;
m:多行模式;
x:扩展模式,忽略正则表达式中的空白字符。

  • 用户同时指定多个互斥参数可选项时,系统按照最后一个参数处理。用户指定 match_parameter 参数选项(i,c,n,m,x)以外的选项时,系统报错。
  • regexp_replace 函数的 replace_string、position、occurrence、match_parameter参数都能省略,若上述 4 个参数中的任何一个省略,省略参数后的所有参数都不能设置,若需要设置后续参数则必须给出所设置参数的上一个参数的值。
  • 由于 sql 语法与 pcre 正则语法都使用反斜杠('\')作为转义符。所以在 pattern中应使用连续两个反斜杠(‘\')作为正则的转义符。
  • regexp_replace 函数不支持递归。
  • 如果 source_char 参数为运算结果而非表的实体列,且此运算结果大于 512个字节,则函数报错。

当前版本不支持group捕获后的替换。

用例

普通字符替换

将st替换成AB

gbase> select regexp_replace('First','st','AB');
+-----------------------------------+
| regexp_replace('First','st','AB') |
+-----------------------------------+
| FirAB                             |
+-----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

正则匹配替换

将手机号替换成*,如下的正则功能只是演示。

gbase> select regexp_replace('我的电话13812345678。','13[6-9][0-9]{8}','***********');
+------------------------------------------------------------------------------+
| regexp_replace('我的电话13812345678。','13[6-9][0-9]{8}','***********')      |
+------------------------------------------------------------------------------+
| 我的电话***********。                                                        |
+------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

指定起始位置

只有字符串指定位置之后的才进行匹配和替换。

gbase> select regexp_replace('我的电话13812345678。13912345678','13[6-9][0-9]{8}','***********',10);
+--------------------------------------------------------------------------------------------+
| regexp_replace('我的电话13812345678。13912345678','13[6-9][0-9]{8}','***********',10)      |
+--------------------------------------------------------------------------------------------+
| 我的电话13812345678。***********                                                           |
+--------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

指定匹配出现的序数

如下分别匹配全部、第1次、第1次出现的匹配。

gbase> select regexp_replace('我的电话13812345678。13912345678','13[6-9][0-9]{8}','***********',1,0);
+---------------------------------------------------------------------------------------------+
| regexp_replace('我的电话13812345678。13912345678','13[6-9][0-9]{8}','***********',1,0)      |
+---------------------------------------------------------------------------------------------+
| 我的电话***********。***********                                                            |
+---------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select regexp_replace('我的电话13812345678。13912345678','13[6-9][0-9]{8}','***********',1,1);
+---------------------------------------------------------------------------------------------+
| regexp_replace('我的电话13812345678。13912345678','13[6-9][0-9]{8}','***********',1,1)      |
+---------------------------------------------------------------------------------------------+
| 我的电话***********。13912345678                                                            |
+---------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select regexp_replace('我的电话13812345678。13912345678','13[6-9][0-9]{8}','***********',1,2);
+---------------------------------------------------------------------------------------------+
| regexp_replace('我的电话13812345678。13912345678','13[6-9][0-9]{8}','***********',1,2)      |
+---------------------------------------------------------------------------------------------+
| 我的电话13812345678。***********                                                            |
+---------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

额外参数忽略大小写

其它参数请参考前面的regexp的样例。

gbase> select regexp_replace('First','fi','##',1,0);
+---------------------------------------+
| regexp_replace('First','fi','##',1,0) |
+---------------------------------------+
| First                                 |
+---------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select regexp_replace('First','fi','##',1,0,'i');
+-------------------------------------------+
| regexp_replace('First','fi','##',1,0,'i') |
+-------------------------------------------+
| ##rst                                     |
+-------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

regexp_instr

获得匹配字符串的位置。

语法

REGEXP_INSTR(source_char,pattern[,position[,occurrence[,return_opt[,match_parameter[,subexpr]]]]])

说明

返回与 pattern 指定的正则表达式相匹配的字符串在源字符串中的位置。

source_char
源字符串。该参数支持的数据类型与 8a 的 replace 函数的 src 参数一致。

pattern
正则表达式。每个正则表达式最多可包含 512 个字节。具体语法规则请参考 PCRE-7.8 版本的语法规则说明

position
开始匹配的位置,如果不指定默认为 1,即从 source_char 的第一个字符开始匹配。position 为一个正整数。

occurrence
正则匹配的序数。是一个非负的整数,默认值为 0。
指定为 0,则替换所有匹配到的字符串;
如果指定为整数 n,则替换第 n 次匹配到的字符串;

return_opt
指定返回值的类型,是一个非负整数,可以是列名,支持的数据类型为整型和能够转换成数字的字符串,转换规则与 GBase8a MPP Cluster 系统的 insert 函数的 pos 参数转换规则一致。不支持小数,若设定为小数,按四舍五入规则处理。默认值为 0。
指定为 0,返回值为匹配位置的第一个字符的位置。
指定为 n,返回匹配的字符串后,紧跟着的第一个字符的位置。

match_parameter
可通过设置该参数改变默认的匹配功能行为。默认情况下“.”不匹配换行符,源字符串被看作一行。参数可选项如下:
i:大小写不敏感;
c:大小写敏感;
n:点号(.)不匹配换行符号;
m:多行模式;
x:扩展模式,忽略正则表达式中的空白字符。

subexpr
兼容Oracle的语法,子正则表达式。
对于含有子表达式的正则表达式,表示正则表达式中的第几个子串是函数目标。subexpr 是正则表达式中圆括号里的字符串片
段,子表达式可嵌套。子表达式按照其左括号出现的顺序编号。
该参数值域范围是 0~9,超过 9,函数返回 0。可以设置成能够转换成数字的字符串,转换规则与GBase 8a MPP Cluster的insert
函数的 pos 参数转换规则一致。不支持列名。不支持小数,若设定为小数,按四舍五入规则处理。默认为 0。
指定为 0,返回与正则表达式匹配的字符的位置,全匹配上返回 1,不匹配返回 0;
指定为大于 0,返回指定的子串的位置。该值大于子串个数时,返回 0;
指定为空,函数返回 null;
源字符串中有括号时,按照正则支持的转义处理。

默认匹配大小写。

样例

普通匹配查找

注意大小写是敏感的。位置从1开始。

gbase> select regexp_instr('FirsT','sT');
+----------------------------+
| regexp_instr('FirsT','sT') |
+----------------------------+
|                          4 |
+----------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select regexp_instr('FirsT','st');
+----------------------------+
| regexp_instr('FirsT','st') |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (Elapsed: 00:00:00.00)

正则匹配

gbase> select regexp_instr('Fir123sT','[0-9]{3}sT');
+---------------------------------------+
| regexp_instr('Fir123sT','[0-9]{3}sT') |
+---------------------------------------+
|                                     4 |
+---------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

指定返回类型参数

默认0是返回字符串出现的位置。如果是>0,则返回这个越过这个匹配的字符串,下一个字符出现的位置。等同于增加了一个length(匹配字符串)。

gbase> select regexp_instr('Fir123sT_123456','[0-9]{3}s',1,1,0);
+---------------------------------------------------+
| regexp_instr('Fir123sT_123456','[0-9]{3}s',1,1,0) |
+---------------------------------------------------+
|                                                 4 |
+---------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select regexp_instr('Fir123sT_123456','[0-9]{3}s',1,1,1);
+---------------------------------------------------+
| regexp_instr('Fir123sT_123456','[0-9]{3}s',1,1,1) |
+---------------------------------------------------+
|                                                 8 |
+---------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

子正则表达式

(123)(4(56)(78)),分别匹配:123,45678,56,78
参数=1:匹配123
gbase> select regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1);
+-----------------------------------------------------------------+
| regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1) |
+-----------------------------------------------------------------+
|                                                               1 |
+-----------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

参数=2,匹配45678

gbase> select regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2);
+-----------------------------------------------------------------+
| regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2) |
+-----------------------------------------------------------------+
|                                                               4 |
+-----------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

参数=3,匹配56

gbase> select regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3);
+-----------------------------------------------------------------+
| regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) |
+-----------------------------------------------------------------+
|                                                               5 |
+-----------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

参数=4,匹配78

gbase> select regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3);
+-----------------------------------------------------------------+
| regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) |
+-----------------------------------------------------------------+
|                                                               5 |
+-----------------------------------------------------------------+

regexp_substr

提取指定字符串的子串。

语法

REGEXP_SUBSTR(source_char,pattern[,position[,occurrence[,match_parameter[,subexpr]]])

说明

找出源字符串中与 pattern 指定的正则表达式相匹配的字符串。

source_char
源字符串。该参数支持的数据类型与 8a 的 replace 函数的 src 参数一致。

pattern
正则表达式。每个正则表达式最多可包含 512 个字节。具体语法规则请参考 PCRE-7.8 版本的语法规则说明

position
开始匹配的位置,如果不指定默认为 1,即从 source_char 的第一个字符开始匹配。position 为一个正整数。

occurrence
正则匹配的序数。是一个非负的整数,默认值为 0。
指定为 0,则替换所有匹配到的字符串;
如果指定为整数 n,则替换第 n 次匹配到的字符串;

match_parameter
可通过设置该参数改变默认的匹配功能行为。默认情况下“.”不匹配换行符,源字符串被看作一行。参数可选项如下:
i:大小写不敏感;
c:大小写敏感;
n:点号(.)不匹配换行符号;
m:多行模式;
x:扩展模式,忽略正则表达式中的空白字符。

subexpr
兼容Oracle的语法,子正则表达式。
对于含有子表达式的正则表达式,表示正则表达式中的第几个子串是函数目标。subexpr 是正则表达式中圆括号里的字符串片
段,子表达式可嵌套。子表达式按照其左括号出现的顺序编号。
该参数值域范围是 0~9,超过 9,函数返回 0。可以设置成能够转换成数字的字符串,转换规则与GBase 8a MPP Cluster的insert
函数的 pos 参数转换规则一致。不支持列名。不支持小数,若设定为小数,按四舍五入规则处理。默认为 0。
指定为 0,返回与正则表达式匹配的字符的位置,全匹配上返回 1,不匹配返回 0;
指定为大于 0,返回指定的子串的位置。该值大于子串个数时,返回 0;
指定为空,函数返回 null;
源字符串中有括号时,按照正则支持的转义处理。

默认匹配大小写。

用例

使用方法和前面的基本相同,特别是reg_instr,这个是返回字符串,那个是返回位置。

普通匹配

返回匹配上的字符串。

gbase> select regexp_substr('FirsT','sT');
+-----------------------------+
| regexp_substr('FirsT','sT') |
+-----------------------------+
| sT                          |
+-----------------------------+
1 row in set (Elapsed: 00:00:00.01)

没有匹配上

返回空字符串。

gbase> select regexp_substr('FirsT','st');
+-----------------------------+
| regexp_substr('FirsT','st') |
+-----------------------------+
|                             |
+-----------------------------+
1 row in set (Elapsed: 00:00:00.00)

正则匹配

gbase> select regexp_substr('Fir123sT','[0-9]{3}sT');
+----------------------------------------+
| regexp_substr('Fir123sT','[0-9]{3}sT') |
+----------------------------------------+
| 123sT                                  |
+----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

子正则表达式substr

如下是子正则表达式(123)(4(56)(78))分别匹配123,45678,56,78的结果。

gbase> select regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1,'i', 1);
+--------------------------------------------------------------+
| regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1,'i', 1) |
+--------------------------------------------------------------+
| 123                                                          |
+--------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1,'i', 2);
+--------------------------------------------------------------+
| regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1,'i', 2) |
+--------------------------------------------------------------+
| 45678                                                        |
+--------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1,'i', 3);
+--------------------------------------------------------------+
| regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1,'i', 3) |
+--------------------------------------------------------------+
| 56                                                           |
+--------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1,'i', 4);
+--------------------------------------------------------------+
| regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1,'i', 4) |
+--------------------------------------------------------------+
| 78                                                           |
+--------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

参考

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions148.htm#SQLRF06300

GBase 8a字符串和日期函数列表索引