用SQL判断字段串中连续出现两个以上的数字,并返回对应的数字
with a as (
select explode(split(regexp_replace(‘sdfsd1232sdf324’,‘[0-9]+’,‘,’),‘,’)) col
) select * from a where LENGTH(col)>1
结果为sdfsd
sdf
取11位手机号码
select regexp_extract(‘MA;IIP=58.211.125.238;IPORT=39236;LIP=172.18.249.61;MAC=NA;IMEI=NA;RMPN=13861311293;UMPN=NA@DSPT-XYD-UA=www.tzt.cn Android Mobile Brow;HN=EMall-Wrs249-61’,‘(\d{11})’,0) as tel_number;
1)国内电话号码已1开头,且第二位在3-9之间,所以有^1[3-9]
2)除上述两位数字外,其余还有9位,所以有\d{9},且以加上结尾符号$
3)可以使用或条件进行更加严格的手机号匹配
select regexp_extract(lower(tel),‘(^1[3-9]\d{9}$)’,0) as tel_number;
select ‘selfstock.tzt.stock.15180445203.-1’
,regexp_extract(‘selfstock.tzt.stock.15180445203.-1’,‘(\d{11})’,0) as tel_num
,regexp_extract(‘selfstock.tzt.stock.15180445203.-1’,‘(1[0-9]{10})’,0)
员工表中入职时间入职日期(hire_date)包含有多个形式的数据,如2021/12/13 18:00:00、2021-12-13 18:00:00、20211213 18:00:00,请将数据统一成一个格式,(如20211213180000)
select REGEXP_REPLACE(‘2021/12/13 18:00:00’, ‘[^0-9]’, ‘’)
结果为20211213180000
提取opstaion的手机号码信息(符合11位长度的数字),若不符合手机号规则,则置空值。
(E@183.135.107.156@15867854587@07396666555,E@223.104.161.221@13884412998,E@39.188.105.52@13906842974@0201111555,3@54E1ADE44071@125116208030@6666666)
Select cust_no,col1 from (
select cust_no,col1 from table lateral view explode(split(opstation,‘@’)) ad as col1
) where length(col1) = 11 and length(regexp_replace(col1,‘[a-z]+’,‘,’)) = 11;
提取数字
with a as (select ‘158元2分’ as je)
select regexp_extract(je,‘([0-9]+)’,1),split(regexp_replace(je,‘([^0-9]+)’,’ ‘),’ ')[1],je from a;
用SQL判断字段串中连续出现两个以上的数字,并返回对应的数字
with a as (
select explode(split(regexp_replace(‘sdfsd1232sdf324’,‘[0-9]+’,‘,’),‘,’)) col
) select * from a where LENGTH(col)>1
结果为sdfsd
sdf
select explode(split(regexp_replace(‘sdfsd1232sdf324’,‘[0-9]+’,‘,’),‘,’))
union all
select explode(split(regexp_replace(‘sdfsd1232sdf324’,‘[a-z]+’,‘,’),‘,’))
select regexp_extract(‘honeymoon’, ‘hon(.*?)(moon)’,2);
select explode(array(‘A’,‘B’,‘C’))
select explode(map(‘a’,1,‘b’,2))
select posexplode(array(‘A’,‘B’,‘C’));
with t2 as ( select ‘1<’ as client_rights,‘a’ as cust_num
union all
select ‘CWe:’ as client_rights,‘a’ as cust_num
)
select
collect_set(t2.client_rights),
– concat_ws(‘’, collect_set(t2.client_rights),
instr(concat_ws(‘’, collect_set(t2.client_rights)), ‘<’),
if(instr(concat_ws(‘’, collect_set(t2.client_rights)), ‘<’) > 0,1,0)
from t2
group by cust_num
取id后面的数字
with a as (select ‘http://127.0.0.1:61062/redmall/html/empd_prodtBuy.html?id=14634061&TZTREQUESTCRC=ACAE284CE7FAD3F42050027FA1CC395F’ as info )
select info,regexp_extract(info,‘id=([0-9]+)’,1) from a
info _c1
http://127.0.0.1:61062/redmall/html/empd_prodtBuy.html?id=14634061&TZTREQUESTCRC=ACAE284CE7FAD3F42050027FA1CC395F 14634061
取字母或者数字
select regexp_extract('MA;IIP=175.153.162.124;IPORT=26377;LIP=192.168.0.7;MAC=FED7A908ECBD;IMEI=NA;RMPN=18699171002;UMPN=18699171002;ICCID=NA;OSV=android10;IMSI=NA@XYD-VER=6.01.030;JYSERVER:172.18.1.133
',‘MAC=([A-Za-z0-9]+)’,1)
,regexp_extract(‘http://127.0.0.1:61062/redmall/html/empd_prodtBuy.html?id=14634061&TZTREQUESTCRC=ACAE284CE7FAD3F42050027FA1CC395F’,‘id=([0-9]+)’,1) ;
– FED7A908ECBD 14634061