本文介绍在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