遇到一个坑的项目，就是以前的客户地址需要把省和市提取出来单独存储，所以就想了一个笨办法，记录一下，避免下次忘记：
 

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