大数据：模糊查询LIKE和正则RLIKE，REGEXP

模糊查询LIKE和正则RLIKE，REGEXP

渡吾。

4963人浏览 · 2023-12-19 17:06:50

渡吾。 · 2023-12-19 17:06:50 发布

一，模糊查询like

LIKE函数 在Hive中执行简单的模糊查询，它可以使用两个通配符和返回结果：

%（百分比）：替换一个或多个字符
_（下划线）：替换单个字符
LIKE 运算符将返回值是布尔值（true/false）返回类型: boolean或null

1.1%通配符使用

%表示任何字符出现任意次数
%通配符是最经常使用的
%在字符的右边如：ka%表示匹配所有以结尾的数据
%在字符的左边如：%ka表示匹配所有以结尾的数据
%在字符的两边如：%ka%表示匹配所有包含ka字符的数据

举例：

1，找出以Ka开头的名字

select Id,Name fromtest where Name like "ka%" ---以Ka开头的名字

2，找出所有包含Miss的名字

select ID，Name from test where name like '%Miss%'

3，查询结果布尔值：判断是否包含oo

select 'breakfast' like 'oo'

1.2 下划线 _ 通配符使用

表示任何字符出现任意次数
通配符是最经常使用的。
在字符的右边如：ka_表示匹配所有以ka开头的三位字符
在字符的左边如：_ka表示匹配所有以ka结尾的三位字符
在字符的两边如：9_1表示匹配所有以9开头以1结尾的三位字符

例子：

1，找出所有以9开始以1结尾的三位数的用户ID

select '901' like '9_1'

2，使用单独一个下划线( _ )通配符的情况下查询因不符合条件则结果为null值

例子：在此表中求name字段

select * from bdp_tmp.test where name like '_'; ---名字为1个字符的人
---此写法等同于'%_'或'_%'
select * from bdp_tmp.test where name like '__'; ---名字为2个字符的人

1.3 %和_组合使用

找出字段中第三个字符起为“otba”的字段

select'football' like '__otba%';

二，正则-RLIKE

2.1 rlike函数语法

语法1: A RLIKE B

返回值：如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE。

语法2: RLIKE(A, B) 表示B是否在A里面

返回值：如果字符串A或者字符串B为NULL，则返回NULL；

操作类型: strings

返回类型: boolean或null

2.2正则表达式规则

. :匹配任意单个字符
* :匹配前面的字符0次或多次
+ :匹配前面的字符1次或多次
? :匹配前面的字符0次或1次
^ :开始位置标记，匹配应在字符串或行的开始
$ :结束位置标记，匹配应在字符串或行的结束
[abc] :匹配方括号中的任何字符(a或b或c)
[a-z] :匹配小写字母a到z中的任何一个
[A-Z] :匹配大写字母A到Z中的任何一个
[0-9] :匹配0到9中的任何一个数字
[^abc] :匹配除a,b,c以外的任何字符
\s :匹配空格字符
\S :匹配非空格字符
\d :匹配数字字符
\D :匹配非数字字符
\w :匹配单词字符（等价于[A-Za-z0-9_]）
\W :匹配非单词字符
x|y :匹配x或y
(pattern) :捕获并记住括号中的pattern的匹配项
(?:pattern) :只进行匹配，不捕获匹配的结果，并且不会分配匹配到的内容到组里

举例：

例1：表示'football'中是否有'ba'
select 'football' rlike 'ba';
true




例2：表示从'footba'开始字段'football' 是否有'footba'
select 'football' rlike '^footba'; 
true


例3：表示'ba'是否在'football'里
select rlike('football','ba'); 
true

2.3 特殊符号案例

例4：\d+前面还要再加\

select '2314' rlike '\\d+';
true

解析：

\d表示匹配任何数字字符，等同于[0-9]

+符号则表示匹配前面的元素一次或多次

\d+正则表达式匹配一个或多个连续的数字

tip:在很多编程语言和环境中，反斜杠\也被用作转义字符，用来取消紧随其后的字符的特殊意义。这意味着当你想使用\d这类正则表达式特殊字符时，你需要告诉解释器你指的是字面上的\，而不是用它来表示转义

在字符串字面量中，要表示一个\字符，通常需要使用两个反斜杠\\。这样第一个\用来“转义”第二个\，使得表达式中实际上有一个字面的反斜杠。所以，在正则表达式中写\d时，实际上需要写成\\d。

这样SQL解析器读到\\时，就知道你的意思是一个字面的\字符，然后它能正确地将\d解释为正则表达式中的一个数字字符

例5：证件地址包含：“村”或“组”或“乡”或“镇”---[ ]

select '广州市' rlike '["村组乡镇"]';
false


select '元村' rlike '["村组乡镇"]';
true

提示：Hive 中可以用 like 和 rlike 进行模糊匹配，like 采用的是 SQL 通配符，而 rlike (regexp)采用的是正则匹配

2.4 否定：A not rilke B

对RLIKE的否定

select 'acv' not rlike '\\d+';
true


select '2314' not rlike '\\d+';
false

三、正则-regexp

3.1 REGEXP

语法规则：A regexp B，功能与RLIKE相同
操作类型: strings
返回类型: boolean或null

# 识别以1为开头，接下来字符全是0-9范围。
select '18920202978' regexp '^1[0-9]+$';  ---^开始位置，$结束位置
true
select '08920202978' regexp '^1[0-9]+$';  ---^开始位置，$结束位置
false

否定：A not regexp B

# 剔除证件地址包含“广州”或“增城”或“番禺”或“花都”或“南沙”的号码
select '广州市' not regexp('广州|增城|番禺|花都|南沙');
true
select '湛江市' not regexp('广州|增城|番禺|花都|南沙');
false

3.2 regexp_extract

语法规则：regexp_extract(string A, string pattern, int index) ，将字符串A 按照 pattern正则表达式 的规则拆分，返回index指定的字符，index从1开始计。
第一参数：要处理的字段
第二参数：需要匹配的正则表达式
第三参数： 0是显示与之匹配的整个字符串 ; 1是显示第一个括号里面的; 2是显示第二个括号里面的字段
操作类型: strings
返回类型: strings

例1:

SELECT regexp_extract(‘100-200’, ‘(\d+)-(\d+)’, 1) ;
>‘100’


select regexp_extract('http://a.m.taobao.com/i41915173660.htm','i([0-9]+)',0);
> i41915173660


> select regexp_extract('http://a.m.taobao.com/i41915173660.htm','i([0-9]+)',1);
> 41915173660

例2:

select regexp_extract(‘Hello, Mr.Gumby : 2016/10/26’,’(.+?),\s(.+?)\s:\s(.+)’,0);
select regexp_extract(‘Hello, Mr.Gumby : 2016/10/26’,’(.+?),\s(.+?)\s:\s(.+)’,1);
select regexp_extract(‘Hello, Mr.Gumby : 2016/10/26’,’(.+?),\s(.+?)\s:\s(.+)’,2);
select regexp_extract(‘Hello, Mr.Gumby : 2016/10/26’,’(.+?),\s(.+?)\s:\s(.+)’,3);

运行结果：

Hello, Mr.Gumby : 2016/10/26
Hello
Mr.Gumby
2016/10/26

3.3 regexp_replace

命令格式：

regexp_replace(source, pattern, replace_string, occurrence)

参数说明：

● source: string类型，要替换的原始字符串

● pattern: string类型常量，要匹配的正则模式，pattern为空串时抛异常

● replace_string:string，将匹配的pattern替换成的字符串

● occurrence: bigint类型常量，必须大于等于0

大于0：表示将第几次匹配替换成replace_string

等于0：表示替换掉所有的匹配子串

其它类型或小于0抛异常

返回值：

将source字符串中匹配pattern子串替换成指定字符串后返回，当输入source, pattern, occurrence参数为NULL时返回NULL，若replace_string为NULL且pattern有匹配，返回NULL，replace_string为NULL但pattern不匹配，则返回原串。

例1：用 '#' 替换字符串中的所有数字

SELECT regexp_replace('01234abcde56789','[0-9]','#') ;
结果：#####abcde#####

用'#'替换字符串中的数字0和9

SELECT regexp_replace(‘01234abcde56789’,’[09]’,’#’) ;
结果：#1234abcde5678#

例2：遇到非小写字母或者数字跳过，从匹配到的第4个值开始替换，替换为''

SELECT regexp_replace('abcdefg123456ABC','[a-z0-9]','',4)
结果：abcefg123456ABC

SELECT regexp_replace('abcDEfg123456ABC','[a-z0-9]','',4)
结果：abcDEg123456ABC

SELECT regexp_replace('abcDEfg123456ABC','[a-z0-9]','',7);
结果：abcDEfg13456ABC

遇到非小写字母或者数字跳过，将所有匹配到的值替换为''
SELECT regexp_replace('abcDefg123456ABC','[a-z0-9]','',0);
结果：DABC

例3：格式化手机号，将+86 13811112222转换为(+86) 138-1111-2222, '+' 在正则表达式中有定义，需要转义。\\1表示引用的第一个组

SELECT regexp_replace('+86 13811112222','(\\+[0-9]{2})( )([0-9]{3})([0-9]{4})([0-9]{4})','(\\1)\\3-\\4-\\5',0);
结果：(+86)138-1111-2222

SELECT regexp_replace("123.456.7890","([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})","(\\1)\\2-\\3",0) ;

SELECT regexp_replace("123.456.7890","([0-9]{3})\\.([0-9]{3})\\.([0-9]{4})","(\\1)\\2-\\3",0) ;

结果：(123)456-7890

例4：将字符用空格分隔开，0表示替换掉所有的匹配子串

SELECT regexp_replace('abcdefg123456ABC','(.)','\\1 ',0) ;
结果：a b c d e f g 1 2 3 4 5 6 A B C

SELECT regexp_replace('abcdefg123456ABC','(.)','\\1 ',2) ;
结果：ab cdefg123456ABC

例5：\\1和\\2的用法

SELECT regexp_replace("abcd","(.*)(.)$","\\1",0) ;
结果：abc

SELECT regexp_replace("abcd","(.*)(.)$","\\2",0);
结果：d

SELECT regexp_replace("abcd","(.*)(.)$","\\1-\\2",0);
结果：abc-d

其他案例：

SELECT regexp_replace("abcd","(.)","\\2",1) 结果为"abcd"，因为pattern中只定义了一个组，引用的第二个组不存在。
SELECT regexp_replace("abcd","(.*)(.)$","\\2",0) 结果为"d"
SELECT regexp_replace("abcd","(.*)(.)$","\\1",0) 结果为"abc"
SELECT regexp_replace("abcd","(.*)(.)$","\\1-\\2",0) 结果为"abc-d"
SELECT regexp_replace("abcd","a","\\1",0)，结果为” \1bcd”，因为在pattern中没有组的定义，所以\1直接输出为字符。

正则符号释义：