大数据：模糊查询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的名字
  

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

复制代码

3，查询结果布尔值：判定是否包罗oo
  

1. select 'breakfast' like 'oo'

复制代码

1.2 下划线 _ 通配符利用

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

例子：
1， 找出全部以9开始以1结尾的三位数的用户ID
  

1. select '901' like '9_1'

复制代码

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

例子：在此表中求name字段
  

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

复制代码

1.3 %和_组合利用

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

1. 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. 例1：表示'football'中是否有'ba'
2. select 'football' rlike 'ba';
3. true
8. 例2：表示从'footba'开始字段'football' 是否有'footba'
9. select 'football' rlike '^footba';
10. true
13. 例3：表示'ba'是否在'football'里
14. select rlike('football','ba');
15. true

复制代码

2.3 特殊符号案例

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

1. select '2314' rlike '\\d+';
2. true

复制代码

分析：
\d表现匹配任何数字字符，等同于[0-9]
+符号则表现匹配前面的元素一次或多次
\d+正则表达式匹配一个或多个一连的数字
tip:在许多编程语言和环境中，反斜杠\也被用作转义字符，用来取消紧随其后的字符的特殊意义。这意味着当你想利用\d这类正则表达式特殊字符时，你必要告诉解释器你指的是字面上的\，而不是用它来表现转义
在字符串字面量中，要表现一个\字符，通常必要利用两个反斜杠\\。如许第一个\用来“转义”第二个\，使得表达式中实际上有一个字面的反斜杠。以是，在正则表达式中写\d时，实际上必要写成\\d。
如许SQL分析器读到\\时，就知道你的意思是一个字面的\字符，然后它能正确地将\d解释为正则表达式中的一个数字字符
例5：证件地址包罗：“村”或“组”或“乡”或“镇”---[ ]
  

1. select '广州市' rlike '["村组乡镇"]';
2. false
5. select '元村' rlike '["村组乡镇"]';
6. true  

复制代码

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

2.4 否定：A not rilke B

对RLIKE的否定
  

1. select 'acv' not rlike '\\d+';
2. true
5. select '2314' not rlike '\\d+';
6. false

复制代码

三、正则-regexp

3.1 REGEXP

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

  

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

复制代码

• 否定：A not regexp B
  

  

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

复制代码

3.2 regexp_extract

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

例1:
  

1. SELECT regexp_extract(‘100-200’, ‘(\d+)-(\d+)’, 1) ;
2. >‘100’
5. select regexp_extract('http://a.m.taobao.com/i41915173660.htm','i([0-9]+)',0);
6. > i41915173660
9. > select regexp_extract('http://a.m.taobao.com/i41915173660.htm','i([0-9]+)',1);
10. > 41915173660

复制代码

例2:
  

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

复制代码

运行结果：

1. Hello, Mr.Gumby : 2016/10/26
2. Hello
3. Mr.Gumby
4. 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：用 '#' 替换字符串中的全部数字

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

复制代码

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

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

复制代码

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

1. SELECT regexp_replace('abcdefg123456ABC','[a-z0-9]','',4)
2. 结果：abcefg123456ABC
4. SELECT regexp_replace('abcDEfg123456ABC','[a-z0-9]','',4)
5. 结果：abcDEg123456ABC
7. SELECT regexp_replace('abcDEfg123456ABC','[a-z0-9]','',7);
8. 结果：abcDEfg13456ABC
10. 遇到非小写字母或者数字跳过，将所有匹配到的值替换为''
11. SELECT regexp_replace('abcDefg123456ABC','[a-z0-9]','',0);
12. 结果：DABC

复制代码

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

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

复制代码

1. SELECT regexp_replace("123.456.7890","([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})","(\\1)\\2-\\3",0) ;
3. SELECT regexp_replace("123.456.7890","([0-9]{3})\\.([0-9]{3})\\.([0-9]{4})","(\\1)\\2-\\3",0) ;
5. 结果：(123)456-7890

复制代码

  例4：将字符用空格分隔开，0表现替换掉全部的匹配子串

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

复制代码

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

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

复制代码

其他案例：

1. SELECT regexp_replace("abcd","(.)","\\2",1) 结果为"abcd"，因为pattern中只定义了一个组，引用的第二个组不存在。
2. SELECT regexp_replace("abcd","(.*)(.)$","\\2",0) 结果为"d"
3. SELECT regexp_replace("abcd","(.*)(.)$","\\1",0) 结果为"abc"
4. SELECT regexp_replace("abcd","(.*)(.)$","\\1-\\2",0) 结果为"abc-d"
5. 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里（尤其是正则表达式里）有特殊字符，如：.,$,"""""""",!,?,*,[],{} 等 ，必要举行必要的转义操作。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。