Hive sql正则获取省/市/区

最新推荐文章于 2023-12-11 12:51:45 发布

xiaojia0309

最新推荐文章于 2023-12-11 12:51:45 发布

阅读量5.7k

点赞数 10

文章标签： mysql spark hive

本文链接：https://blog.csdn.net/xiaojia0309/article/details/121543864

版权

在网上搜集关于sql正则表达式取类似字段发现并没有直接可以参考的正则表达式，特此分享给大家。

原版demo，如下图。

想要效果：省、市、区/县，如下图。

获取起来对我来说并不容易.. 特此写出sql 分享给大家。

recipient_address为字段名称

select 
 recipient_address,
 case when recipient_address like '%省%' then regexp_extract(recipient_address 
 ,'[^省]+省',0)  
 when  recipient_address like '%市%市%' then regexp_extract(recipient_address ,'[^ 市]+市',0)
 when  recipient_address like '%自治区%市%区%' then regexp_extract(recipient_address ,'[^自治区]+自治区',0)
 when  recipient_address like '%自治区%市%县%' then regexp_extract(recipient_address ,'[^自治区]+自治区',0)
end as province,
case when recipient_address like '%省%' then regexp_extract(recipient_address ,'[^省]+市',0)  
 when recipient_address like '%市%市%' then regexp_extract(recipient_address ,'[^市]+市',0)  
 when recipient_address like '%自治区%市%区%' then regexp_extract(recipient_address ,'[^自治区]+市',0)  
 when recipient_address like '%自治区%市%县%' then regexp_extract(recipient_address ,'[^自治区]+市',0) 
end as city,
case
when recipient_address like '%省%' or recipient_address like '%市%市%' then regexp_extract(recipient_address ,'[^市]+区',0) 
when recipient_address like '%市%市%' or recipient_address like '%市%市%' then regexp_extract(recipient_address ,'[^市]+区',0)  
when recipient_address like '%自治区%市%区%' or recipient_address like '%市%市%' then regexp_extract(recipient_address ,'[^市]+区',0)  
when recipient_address like '%自治区%市%县%' or recipient_address like '%市%市%' then regexp_extract(recipient_address ,'[^市]+县',0)    
end as distrist
from(
select 
case when recipient_address like '%省%' then regexp_extract(recipient_address ,'[^省]+省[^市]+市[^区]+区',0) 
when recipient_address like '%自治区%市%区%' then regexp_extract(recipient_address ,'[^自治区]+自治区[^市]+市[^区]+区',0) 
when recipient_address like '%自治区%市%县%' then regexp_extract(recipient_address ,'[^自治区]+自治区[^市]+市[^县]+县',0) 
when recipient_address like '%市%市%' then regexp_extract(recipient_address ,'[^市]+市[^市]+市[^区]+区',0) 
else recipient_address
end as recipient_address
from 表名称 
) a

希望对大家有帮助！谢谢！