正则表达式
2020-08-22 # SQL # SQL

一、正则匹配

RLIKE、REGEXP
  • 通配符匹配（B为简单通配符表达式）
    A LIKE B
    A NOT LIKE B / NOT A LIKE B
    _ 表示任意单个字符（1个）
    % 表示任意数量字符（0个或多个）
  • 正则匹配（B为正则表达式）
    A RLIKE B
    REGEXP_EXTRACT(string str,string B,int index)
    元字符描述
    元字符 描述
    \ 转义字符 \\n匹配\n \\匹配\
    ^ 字符串开始位置 如果设置了RegExp对象的Multiline属性，^也匹配\n或\r之后的位置
    $ 字符串结束位置 如果设置了RegExp对象的Multiline属性，$也匹配\n或\r之前的位置
    * {0，}，匹配前面子表达式任意次 例zo*能匹配z、zo、zoo等
    + {1，}，匹配前面子表达式一次或多次 例zo*能匹配zo、zoo，但不能匹配z
    ? {0,1}，匹配前面子表达式0次或1次 例do(es)?可以匹配do、does
    {n} n是一个非负整数，匹配确定的n次 例o{2}不能匹配Bob但可以匹配food
    {n，} 至少匹配n次 例o{2}不能匹配Bob但可以匹配foooood o{1,}等价于o+ o{0,}等价于o*
    {n,m} 最少匹配n次且最多匹配m次 例o{1,3}将匹配foooood中的前3个o o{0,1}等价于o?
    x|y 匹配x或y 例(z|f)ood则匹配zood或food
    [xyz] 匹配所包含的任意一个字符 例[abc]可以匹配plain中的a
    [^xyz] 负值字符集合，匹配未包含的任意集合 例[^abc]匹配plain中的plin
    [a-z] 匹配指定范围内的任意字符 例[a-z]可以匹配a到z范围内的任意小写字符
    [^a-z] 匹配任意不在指定范围内的任意字符
    \b 匹配单词边界，就是指单词和空格间的位置 例er\b可以匹配never但不能匹配verb
    \B 匹配非单词边界 例er\B可以匹配verb但不能匹配never
    \cx 匹配由x指明的控制字符 例\cM匹配Control-M或回车符 x值必须为A-Z或a-z 否则c将被视为一个原义的c字符
    \d 匹配一个数字字符 等价于[0-9]
    \D 匹配一个非数字字符 等价于[^0-9]
    \f 匹配一个换页符 等价于\x0c和\cL
    \n 匹配一个换行符 等价于\x0a和\cJ
    \r 匹配一个回车符 等价于\x0d和\cM
    \s 匹配任何不可见字符 包括空格、制表符、换页符等 等价于[\f\n\r\t\v]
    \S 匹配任何可见字符 等价于[^\f\n\r\t\v]
    \t 匹配一个制表符 等价于\x09和\cI
    \v 匹配一个垂直制表符 等价于\x0b和\cK
    \w 匹配包括下划线的任何单次字符 单词使用Unicode字符集
    \W 匹配任何非单词字符 等价于[^A-Za-z0-9]

Example：

抽取20200831所发的帖子中，标题和内容均包含关键词（电影、电视剧、综艺、彩蛋、剧评、影评、路透）中的任一个，且在标题中不包含关键词（通知、公告、求粉、刷分、灌水、刷经验、抢楼、盖楼、签到、打卡、签个到、求购、代理、中奖）的帖子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 帖子id、发帖人id、发帖时间、标题、包含的第一个关键词、包含的关键词个数
select
  tid,
  uid,
  created_at,
  title,
  regexp_extract(title, '.\*(电影|电视剧|综艺|彩蛋|剧评|影评|路透).\*', 1) as first_keyword,
  size(split(title, '电影|电视剧|综艺|彩蛋|剧评|影评|路透')) -1 as nums
from
  Table
where
  created_at >= '2020-08-31 00:00:00'
  and created_at < '2020-09-01 00:00:00' 
  and title rlike '.\*(电影|电视剧|综艺|彩蛋|剧评|影评|路透).\*'
  and content rlike '.\*(电影|电视剧|综艺|彩蛋|剧评|影评|路透).\*' 
  and not title rlike '.\*(通知|公告|求粉|刷分|灌水|刷经验|抢楼|盖楼|签到|打卡|签个到|求购|代理|中奖).\*'

二、常用应用实例

描述 正则表达式
邮政编码 ^[0-9][0-9][0-9][0-9][0-9][0-9]$ 或 ^[0-9]{6} 或 ^\d{6}$
用户名规则（字母数字下划线） ^\w{8,10}$
电话号码（11为数字 可以+86开头） ^(+86)?\s?\d{11}$
身份证号码 ^\d{15}(\d{2}[0-9xX])?$
日期（2020-01-01） ^\d{4}-\d{2}-\d{2}$ 或 ^\d{4}(-\d{2}){2}$
IP地址 ^\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}$
Email地址 ^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$
数字 ^[0-9]*$
n位的数字 ^\d{n}$
至少n位的数字 ^\d{n,}$
m-n位的数字 ^\d{m,n}$
零和非零开头的数字 ^(0|[1-9][0-9]*)$
非零开头的最多带两位小数的数字 ^([1-9][0-9])*+(.[0-9]{1,2})?$
带1-2位小数的正数或负数 ^(-)?\d+(.\d{1,2})?$
正数、负数、小数 ^(\-|\+)?\d+(\.\d+)?$
有两位小数的正实数 ^[0-9]+(.[0-9]{2})?$
有1-3位小数的正实数 ^[0-9]+(.[0-9]{1,3})?$
非零的正整数 ^[1-9]\d*$
非零的负整数 ^-[1-9]\d*$
非负整数 ^\d+$
非正整数 ^-[1-9]\d*|0$
浮点数 ^(-?\d+)(\.\d+)?$
汉字 ^[\u4e00-\u9fa5]{0,}$
英文和数字 ^[A-Za-z0-9]+$
长度为3-20的所有字符 ^.{3,20}$
由26个英文字母组成的字符串 ^[A-Za-z]+$
由数字、26个英文字母或者下划线组成的字符串 ^\w+$
中文、英文、数字包括下划线 ^[\u4E00-\u9FA5A-Za-z0-9_]+$
可以输入含有^%&’,;等字符 [^%&’,;=?$\x22]+
禁止输入含有~的字符 [^~\x22]+
PREV
2020-08-22 # SQL # SQL
NEXT