Sql 如何批量把邮箱地址更新成邮箱前缀？
例如 表名 list 字段email_title
如何把字段email_title里面的邮件地址数据，批量操作成邮件前缀，即删除@及@后面的所有字符。
adfdf@163.com 变成 adfdf
fdf@sina.cn 变成 fdf
求批量更新的sql
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-20 13:01:14
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[list]
if object_id('[list]') is not null drop table [list]
go
create table [list]([email_title] varchar(13))
insert [list]
select 'adfdf@163.com' union all
select 'fdf@sina.cn'
--------------开始查询--------------------------
select SUBSTRING([email_title],1,PATINDEX('%@%',[email_title])-1)
from [list]
----------------结果----------------------------
/*
-------------
adfdf
fdf
*/
--drop table tb
--go
create table tb(v nvarchar(20))
insert into tb
select 'adfdf@163.com'
union all select 'fdf@sina.cn'
;with t
as
(
select v,left(v,CHARINDEX('@',v)-1) vv
from tb
)
--更新数据
update t
set v = vv
--更新后,查询数据,已经更新
select *
from tb
/*
v
adfdf
fdf
*/
select 'adfdf@163.com' email_title
into #list
union all select 'fdf@sina.cn'
------------开始查询--------------------------
select *
from #list
;with t as
(
select [email_title]
,stuff([email_title],PATINDEX('%@%',[email_title]),LEN([email_title])-PATINDEX('%@%',[email_title])+1,'') email_title1
from #list
)
update #list
set email_title=email_title1
from t
join #list t2
on t.email_title=t2.email_title
select *
from #list
----------------结果----------------------------
/*
-------------
adfdf
fdf
*/
--出现这错误,说明你的某些记录中email_title字段没有关键字@
update list set email_title=case when PATINDEX('%@%',[email_title])>1
then substring([email_title],1,PATINDEX('%@%',[email_title])-1)
else email_title end
