截取电话

---------------------------------------------------------------------------电话

update z_beijing_all_web33 set tellphone=substr(web_text,instr(web_text,'电话',1)-10,30)
where is_yellp is null and web_text like '%电话%' and  tellphone is null



update z_beijing_all_web33 set tellphone=substr(web_text,instr(web_text,'Tel',1)-10,30)
where is_yellp is null and web_text like '%Tel%' and  tellphone is null
--截取电话第1 步
update z_beijing_all_web33 set tellphone=substr(tellphone,instr(tellphone,'电话','1'),length(tellphone))
where tellphone is not null and is_yellp is null

update z_beijing_all_web33 set tellphone=substr(tellphone,instr(tellphone,'Tel','1'),length(tellphone))
where tellphone is not null and is_yellp is null and tellphone like '%Tel%' and tellphone not like '%电话%'

update z_beijing_all_web33 set tellphone= replace(tellphone,'Tel','电话')
where tellphone is not null and is_yellp is null and tellphone like '%Tel%' and tellphone not like '%电话%'

--截取电话第2 步
update z_beijing_all_web33 set tellphone=replace(tellphone,'？','')
where tellphone is not null and is_yellp is null  and tellphone like '%？%'
--截取电话第3 步
update z_beijing_all_web33 set tellphone=replace(tellphone,'?','')
where tellphone is not null and is_yellp is null  and tellphone like '%?%'
--截取电话第4 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'传真','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%传真%'
--截取电话第5 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'地址','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%地址%'
--截取电话第6 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'网站','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%网站%'
--截取电话第7 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'地图','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%地图%'
--截取电话第8 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'关于我们','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%关于我们%'
--截取电话第9 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'Fa','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%Fa%'
--截取电话第10 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'电子','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%电子%'
--截取电话第11 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'手机','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%手机%'
--截取电话第12 步
update z_beijing_all_web33 set tellphone=substr(tellphone,instr(tellphone,'电话：','1'),length(tellphone))
where tellphone is not null and is_yellp is null and tellphone like '%电话：%'
--截取电话第13 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'投诉电话','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%投诉电话%'
--截取电话第14 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'首','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%首%'
--截取电话第15 步
update z_beijing_all_web33 set tellphone=replace(tellphone,'　','')
where tellphone is not null and is_yellp is null and tellphone like '%　%'
--截取电话第16 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'周','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%周%'
--截取电话第17 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'邮箱','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%邮箱%'
--截取电话第18 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'公司','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%公司%'
--截取电话第19 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'传','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%传%'
--截取电话第20 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'联系','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%联系%'
--截取电话第21 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'E','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%E%'
--截取电话第22 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'邮编','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%邮编%'
--截取电话第23 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'客服','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%客服%'
--截取电话第24 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'在线','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%在线%'
--截取电话第25 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'QQ','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%QQ%'
--截取电话第26 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'Pow','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%Pow%'
--截取电话第27 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'招标','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%招标%'
--截取电话第28 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'www','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%www%'
--截取电话第29 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'校长','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%校长%'
--截取电话第30 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'服务','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%服务%'
--截取电话第31 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'信箱','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%信箱%'
--截取电话第32 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'信息','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%信息%'
--截取电话第33 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'获取','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%获取%'
--截取电话第34 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'品牌','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%品牌%'
--截取电话第35 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'售后','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%售后%'
--截取电话第36 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'技术','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%技术%'
--截取电话第37 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'注册','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%注册%'
--截取电话第38 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'客户','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%客户%'
--截取电话第39 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'标签','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%标签%'
--截取电话第40 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'营业','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%营业%'
--截取电话第41 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'Copy','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%Copy%'
--截取电话第42 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'邮件','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%邮件%'
--截取电话第43 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'关注','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%关注%'
--截取电话第44 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'北京','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%北京%'
--截取电话第45 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'版权所有','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%版权所有%'
--截取电话第46 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'物','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%物%'
--截取电话第47 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'服','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%服%'
--截取电话第48 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'国','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%国%'
--截取电话第49 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'中','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%中%'
--截取电话第50 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'经营者','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%经营者%'
--截取电话第51 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'京','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%京%'
--截取电话第52 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'举报','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%举报%'
--截取电话第53 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'业务','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%业务%'
--截取电话第54 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'邮政','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%邮政%'
--截取电话第55 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'官方','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%官方%'
--截取电话第56 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'咨询','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%咨询%'
--截取电话第57 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'关闭','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%关闭%'
--截取电话第58 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'版','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%版%'
--截取电话第59 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'返','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%返%'
--截取电话第60 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'分享','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%分享%'
--截取电话第61 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'招生','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%招生%'
--截取电话第61 步
update z_beijing_all_web33 set tellphone=substr(tellphone,0,instr(tellphone,'官','1')-1)
where tellphone is not null and is_yellp is null and tellphone like '%官%'



--截取电话第62 步
update z_beijing_all_web33 set tellphone=null
where
tellphone is not null and is_yellp is null
and tellphone not like '%0%'
and tellphone not like '%1%'
and tellphone not like '%2%'
and tellphone not like '%3%'
and tellphone not like '%4%'
and tellphone not like '%5%'
and tellphone not like '%6%'
and tellphone not like '%7%'
and tellphone not like '%8%'
and tellphone not like '%9%'