一,模糊查询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-2222SELECT 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里(尤其是正则表达式里)有特殊字符,如:.,$,"""""""",!,?,*,[],{} 等 ,需要进行必要的转义操作。