Hive sql正则获取省/市/区

在网上搜集关于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 

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

 

 

  • 10
    点赞
  • 25
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
HiveSQL中,可以使用正则表达式进行模式匹配和替换操作。HiveSQL提供了一些内置函数来处理正则表达式,其中包括regexp_replace函数。该函数的语法如下: regexp_replace(string A, string B, string C) 其中,A是要进行替换的字符串,B是要匹配的正则表达式,C是要替换的内容。该函数会将字符串A中符合正则表达式B的部分替换为C。 需要注意的是,在HiveSQL中,正则表达式的语法与Java的正则表达式语法相似。如果需要使用特殊字符,可能需要进行转义。可以参考Java正则表达式的语法来编写HiveSQL中的正则表达式。 参考资料: \[1\] regexp_replace函数的说明,https://blog.csdn.net/weixin_37536446/article/details/81053172 #### 引用[.reference_title] - *1* [hive sql正则表达式总结](https://blog.csdn.net/weixin_40659514/article/details/127105119)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insert_down28v1,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [hive sql 正则表达式](https://blog.csdn.net/u011919863/article/details/119900339)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insert_down28v1,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值