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

Logo

开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!

更多推荐