sql正则提取固定内容

原创已于 2023-04-20 11:42:03 修改 · 1.3w 阅读

40 ·

CC 4.0 BY-SA版权

文章标签：

#sql #正则表达式

于 2021-02-20 10:50:30 首次发布

sql 专栏收录该内容

22 篇文章

订阅专栏

regexp_extract

语法: regexp_extract(string subject, string pattern, int index)

返回值: string

说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

第一参数string subject：要处理的字段
第二参数string pattern: 需要匹配的正则表达式
第三个参数int index:
0是显示与之匹配的整个字符串
1 是显示第一个括号里面的
2 是显示第二个括号里面的字段…

使用示例
select

regexp_extract(‘x=a3&x=18abc&x=2&y=3&x=4’,‘x=([0-9]+)([a-z]+)’,0), – x=18abc

regexp_extract(‘x=a3&x=18abc&x=2&y=3&x=4’,‘^x=([a-z]+)([0-9]+)’,0), – x=a3

regexp_extract(‘https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive’,‘id=([0-9]+)’,0), – id=522228774076

regexp_extract(‘https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive’,‘id=([0-9]+)’,1), – 522228774076

regexp_extract(‘http://a.m.taobao.com/i41915173660.htm’,‘i([0-9]+)’,0), – i41915173660

regexp_extract(‘http://a.m.taobao.com/i41915173660.htm’,‘i([0-9]+)’,1) – 41915173660

select regexp_extract(‘hitdecisiondlist’,‘(i)(.*?)(e)’,0) ;

结果：itde

select regexp_extract(‘hitdecisiondlist’,‘(i)(.*?)(e)’,1) ;

结果：i

select regexp_extract(‘hitdecisiondlist’,‘(i)(.*?)(e)’,2) ;

结果：td

select regexp_extract(‘x=a3&x=18abc&x=2&y=3&x=4’,‘x=([0-9]+)([a-z]+)’,2) from default.dual;

结果：abc

提取固定数字

提取没有清洗数值中的数字部分

存在没有清洗的数值value：
1、示例内容:
‘身份证号 : 0123456789876543210，完毕’
由汉字+标点+数字+标点+汉字组成
2、我们只想提取其中部分数字内容

select regexp_extract('身份证号 : 0123456789876543210，完毕','.*([0-9]{19}).*',1)

.* ：代表任意多次匹配任意值
[0-9]：代表从0-9数字中匹配
{19}：代表匹配19次

0123456789876543210

提取手机号

select regexp_extract('身份证号 : 0123456789876543210，完毕，手机号：13012341234','.*([1][35678][0-9]{9}).*$',1)

手机号是规则的，11位，并且1开头[35678]是第二位
[35678]：代表手机号的第二位，也可自行修改
[0-9]：代表匹配0-9数字
{9}：代表剩余的9位数字匹配九次

13012341234

split_part

select split_part (‘/abc/required_string/2/’, ‘/’, 3)
返回：required string

SUBSTRING_INDEX

select 
SUBSTRING_INDEX('#住宿和餐饮业#餐饮业#餐饮#河北省#保定市#雄县', '#', 2),
SUBSTRING_INDEX(SUBSTRING_INDEX('#住宿和餐饮业#餐饮业#餐饮#河北省#保定市#雄县', '#', 2), '#', -1) AS result,
SUBSTRING_INDEX('#住宿和餐饮业#餐饮业#餐饮#河北省#保定市#雄县', '#', 3),
SUBSTRING_INDEX(SUBSTRING_INDEX('#住宿和餐饮业#餐饮业#餐饮#河北省#保定市#雄县', '#',3), '#', -1) AS result2

"_c0"	"result"	"_c2"	"result2"
"#住宿和餐饮业"	"住宿和餐饮业"	"#住宿和餐饮业#餐饮业"	"餐饮业"