遇到一个坑的项目,就是以前的客户地址需要把省和市提取出来单独存储,所以就想了一个笨办法,记录一下,避免下次忘记:
select CHARINDEX('省',T_Address) from CustomerInfo
// 获取省省份,地址:xxx省xxx
select SUBSTRING(T_Address,1,CHARINDEX('省',T_Address)) from CustomerInfo where CHARINDEX('省',T_Address)>0
// 获取自治区,地址:xxx自治区xxx
select SUBSTRING(T_Address,1,CHARINDEX('自治区',T_Address)+2) from CustomerInfo where CHARINDEX('自治区',T_Address)>0
// 获取市,地址:xxx市xxx
select SUBSTRING(T_Address,1,CHARINDEX('市',T_Address)) from CustomerInfo where CHARINDEX('市',T_Address)>0 and CHARINDEX('省',T_Address)<=0 and CHARINDEX('自治区',T_Address)<=0
// 获取市,地址:xxx省xxx市xxx
select SUBSTRING(T_Address,CHARINDEX('省',T_Address)+1,CHARINDEX('市',T_Address)-CHARINDEX('省',T_Address)) from CustomerInfo where CHARINDEX('省',T_Address)>0 and CHARINDEX('市',T_Address)>0
// 获取市,地址:xxx自治区xx市xxx
select SUBSTRING(T_Address,CHARINDEX('自治区',T_Address)+3,CHARINDEX('市',T_Address)-CHARINDEX('自治区',T_Address)-2) from CustomerInfo where CHARINDEX('自治区',T_Address)>0 and CHARINDEX('市',T_Address)>0
更新的时候直接用:
// 提取省份并更新
update CustomerInfo set T_Province=SUBSTRING(T_Address,1,CHARINDEX('省',T_Address)) where CHARINDEX('省',T_Address)>0
// 提取以xx市开头的市并更新
update CustomerInfo set T_City=SUBSTRING(T_Address,1,CHARINDEX('市',T_Address)) from CustomerInfo where CHARINDEX('市',T_Address)>0 and CHARINDEX('省',T_Address)<=0 and CHARINDEX('自治区',T_Address)<=0
// 提取省下的市 并更新(xxx省xx市)
update CustomerInfo set T_City=SUBSTRING(T_Address,CHARINDEX('省',T_Address)+1,CHARINDEX('市',T_Address)-CHARINDEX('省',T_Address)) from CustomerInfo where CHARINDEX('省',T_Address)>0 and CHARINDEX('市',T_Address)>0