截取地址

---------------------------------------------------------------------------------------------地址
--起步
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 步
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 '%当前位置%'
--截取地址第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 '%？%'