截取位址

---------------------------------------------------------------------------------------------位址

--起步

update z_beijing_all_web33 set ent_address=substr(web_text,instr(web_text,'位址',1)-10,50)

where is_yellp is null and web_text like '%位址%' and ent_address is null

--截取位址第1 步

update z_beijing_all_web33 set ent_address=substr(ent_address,instr(ent_address,'位址','1'),length(ent_address))

where ent_address is not null

--截取位址第2 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'郵箱','1')-1)

where ent_address is not null and ent_address like '%郵箱%'

--截取位址第3 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'郵編','1')-1)

where ent_address is not null and ent_address like '%郵編%'

--截取位址第4 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'座機','1')-1)

where ent_address is not null and ent_address like '%座機%'

--截取位址第5 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'手機','1')-1)

where ent_address is not null and ent_address like '%手機%'

--截取位址第6 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'聯系電話','1')-1)

where ent_address is not null and ent_address like '%聯系電話%'

--截取位址第7 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'電話：','1')-1)

where ent_address is not null and ent_address like '%電話：%'

--截取位址第8 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'檢視地圖','1')-1)

where ent_address is not null and ent_address like '%檢視地圖%'

--截取位址第9 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'地圖','1')-1)

where ent_address is not null and ent_address like '%地圖%'

--截取位址第10 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'郵政編碼','1')-1)

where ent_address is not null and ent_address like '%郵政編碼%'

--截取位址第11 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'Copyrigh','1')-1)

where ent_address is not null and ent_address like '%Copyrigh%'

--截取位址第12 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'特色','1')-1)

where ent_address is not null and ent_address like '%特色%'

--截取位址第13 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'技術支援','1')-1)

where ent_address is not null and ent_address like '%技術支援%'

--截取位址第14 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'網址','1')-1)

where ent_address is not null and ent_address like '%網址%'

--截取位址第15 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'附近的商戶','1')-1)

where ent_address is not null and ent_address like '%附近的商戶%'

--截取位址第16 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'主營分類','1')-1)

where ent_address is not null and ent_address like '%主營分類%'

--截取位址第17 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'聯系人','1')-1)

where ent_address is not null and ent_address like '%聯系人%'

--截取位址第18 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'咨詢熱線','1')-1)

where ent_address is not null and ent_address like '%咨詢熱線%'

--截取位址第19 步

update z_beijing_all_web33 set ent_address=substr(ent_address,0, instr(ent_address,'目前位置','1')-1)

where ent_address is not null and ent_address like '%目前位置%'

--截取位址第20 步

--截取位址第21 步

update z_beijing_all_web33 a set a.is_yellp ='2'

where ent_address is not null and ent_address not like '%北京%' and ent_address like '%省%' and ent_address like '%市%'

--

update z_beijing_all_web33 set ent_address = replace(ent_address,'？','') where ent_address like '%？%'