update 表
set 字段=replace(字段,'.','')
+'.'
+(
case when 字段 like '%sina%' or
字段 like '%126%' or
.............
then 'com'
else 'net'
end
)
set 字段=replace(字段,'.','')
+'.'
+(
case when 字段 like '%sina%' or
字段 like '%126%' or
.............
then 'com'
else 'net'
end
)
在关键字 'then' 附近有语法错误。
declare @t1 table(mail varchar(50))
insert @t1 select '@sina.com'
insert @t1 select '@163.com'
insert @t1 select '@263.net'
insert @t1 select '@mail.online.sh.cn'
insert @t1 select '@sohu.com'
insert @t1 select '@21cn.com'
insert @t1 select '@yahoo.com.cn'
insert @t1 select '@hotmail.com'
insert @t1 select '@zhjy.net'
insert @t1 select '@fzgz.com'
insert @t1 select '@5088mp.com'
insert @t1 select '@sina.com'
insert @t1 select '@zyzj.gov.cn'
insert @t1 select '@cngm.org.cn'
insert @t1 select '@126.com'declare @t table(mail varchar(50))
insert @t select '[email protected]..'
insert @t select 'zkagri@126...'
insert @t select '[email protected]..'
insert @t select '[email protected]..'
insert @t select '[email protected]...'
insert @t select '[email protected]..'
insert @t select 'root@fzgz...'
insert @t select 'subr@sina.'
insert @t select 'subr@sina..'
insert @t select 'subr@sina...'
insert @t select 'subr@sina....'
insert @t select 'subr@126.'
insert @t select 'subr@126..'
insert @t select 'subr@126...'
insert @t select 'subr@126....'
insert @t select 'subr@163.'
insert @t select 'subr@163..'
insert @t select 'subr@163...'
insert @t select 'subr@163....'
insert @t select 'subr@263.'
insert @t select 'subr@263..'
insert @t select 'subr@263...'
insert @t select 'subr@263....'
insert @t select 'subr@sohu.'
insert @t select 'subr@sohu..'
insert @t select 'subr@sohu...'
insert @t select 'subr@sohu....'
insert @t select 'subr@yahoo.'
insert @t select 'subr@yahoo..'
insert @t select 'subr@yahoo...'
insert @t select 'subr@yahoo....'
insert @t select 'subr@hotmail.'
insert @t select 'subr@hotmail..'
insert @t select 'subr@hotmail...'
insert @t select 'subr@hotmail....'
insert @t select 'subr@21cn.'
insert @t select 'subr@21cn..'
insert @t select 'subr@21cn...'
insert @t select 'subr@21cn....'--更新
update @t set mail=left(a.mail,charindex('@',a.mail)-1)+b.mail from @t a,@t1 b where substring(a.mail,charindex('@',a.mail),4)=left(b.mail,4)
select distinct * from @t--结果
mail
--------------------------
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected](所影响的行数为 15 行)