0
0

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

一，模糊查询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直接输出为字符。

正则符号释义：

提示：正则在shell脚本中的应用

1，shell脚本对于$符号会处理成变量名前缀而非json提取串的变量标识，因此最终传送给引擎执行的SQL语句和在交互查询上看到的SQL语句并不完全一样。

2，不同的计算引擎对特殊字符的处理会存在差别，如在正则匹配中，对于特殊字符'.' ，presto（通常是默认的交互查询引擎) 支持的是直接一个点，不用特别处理；而hive和spark需要写成 [.]或者\.。

建议：

1，在shell脚本中，1）如果SQL里有$且非变量，需要转义为$; 2）字符\本身是shell用于转义操作的字符，需要多重转义。

2, 查看SQL里（尤其是正则表达式里）有特殊字符，如：.,$,"""""""",!,?,*,[],{} 等 ，需要进行必要的转义操作。

标签： sql hive mysql
本文转载自: https://blog.csdn.net/weixin_63058507/article/details/135088949
版权归原作者 丸叽丸叽 所有， 如有侵权，请联系我们删除。
登录后发布评论

“大数据：模糊查询LIKE和正则RLIKE，REGEXP”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

ELK 8.15.3 版本Logstash和Kibana与KS配置SSL实现https安全连接

服务攻防之开发组件安全

ubuntu设置共享文件夹

在 Spring 中编写单元测试

【The Art of Unit Testing 3_自学笔记04】第二章：编写第一个单元测试（下）

git submodule update --init --recursive 解读

Git的基本使用入门

文章导航