34,590
社区成员
发帖
与我相关
我的任务
分享
--出现这错误,说明你的某些记录中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
谢谢版本,不过执行时系统提示“substring 函数传递了无效的Length参数”[/quote]----------------------------------------------------------------
-- 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'
update list
set email_title=SUBSTRING([email_title],1,PATINDEX('%@%',[email_title])-1)
SELECT * FROM list
/*
email_title
-------------
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
*/
我也写一个
--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
*/
;WITH cte AS (
select SUBSTRING([email_title],1,PATINDEX('%@%',[email_title])-1)New_email_title,id --假设你这里的主键是ID
from [list])
UPDATE list
SET list.[email_title]=cte.New_email_title
FROM list INNER JOIN cte ON list.id=cte.id
----------------------------------------------------------------
-- 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
*/