update example set title=replace(title,'ABC','123'), content=replace(content,'ABC','123')
--1.替换一个表中的所有字段中的ABC为123declare @s nvarchar(4000),@tbname sysname select @s=N'',@tbname=N'jobs' --要处理的表名 select @s=@s+N','+quotename(a.name)+N'=replace('+quotename(a.name)+N',N''ABC'',N''123'')' from syscolumns a,systypes b where a.id=object_id(@tbname) and a.xusertype=b.xusertype and b.name like N'%char' set @s=stuff(@s,1,1,N'') exec(N'update '+@tbname+' set '+@s)
update A set Title=replace(Title,'ABC','123'),content=replace(Content,'ABC','123') update B set Title=replace(Title,'ABC','123'),content=replace(Content,'ABC','123')
--2.替换当前数据库的所有表中的所有字段中的ABC为123 exec sp_msforeachtable @command1=N' declare @s nvarchar(4000),@tbname sysname select @s=N'''',@tbname=N''?'' select @s=@s+N'',''+quotename(a.name)+N''=replace(''+quotename(a.name)+N'',N''''ABC'''',N''''123'''')'' from syscolumns a,systypes b where a.id=object_id(@tbname) and a.xusertype=b.xusertype and b.name like N''%char'' if @@rowcount>0 begin set @s=stuff(@s,1,1,N'''') exec(N''update ''+@tbname+'' set ''+@s) end'
declare @id int declare @nob int declare @tbname nvarchar(100)set @id=1 set @nob=(select max(id) from temp) select id=identity(int,1,1),name into temp from temp..sysobjects where xtype='u' set @tbname=(select name from temp where id=@id) while @id<=@nob begin if exists(select * from syscolumns where id=object_id('@tbname') and name='title') begin exec('update '+@tbname+' set title=replace(title,''ABC'',''123'') and content=replace(content,''ABC'',''123'')') end else begin exec('update '+@tbname+' set column1=replace(column1,''ABC'',''123'') and column2=replace(column2,''ABC'',''123'')') end set @id=@id+1 set @tbname=(select name from temp where id=@id) end drop table temp
不用这么复杂用存储语句 没必要搞这么复杂 UPDATE Example SET aa = REPLACE(Title, 'ABC', '123'), bb = REPLACE(Content, 'ABC', '123') WHERE (CHARINDEX('ABC', Title) > 0) OR (CHARINDEX('ABC', Content) > 0)
其实就一句话也可以 UPDATE Example SET aa = REPLACE(Title, 'ABC', '123'), bb = REPLACE(Content, 'ABC', '123')
update example set title=replace(title,'ABC','123'), content=replace(content,'ABC','123')
set title=replace(title,'ABC','123'),
content=replace(content,'ABC','123')
select @s=N'',@tbname=N'jobs' --要处理的表名
select @s=@s+N','+quotename(a.name)+N'=replace('+quotename(a.name)+N',N''ABC'',N''123'')'
from syscolumns a,systypes b
where a.id=object_id(@tbname)
and a.xusertype=b.xusertype
and b.name like N'%char'
set @s=stuff(@s,1,1,N'')
exec(N'update '+@tbname+' set '+@s)
--2.替换当前数据库的所有表中的所有字段中的ABC为123
exec sp_msforeachtable @command1=N'
declare @s nvarchar(4000),@tbname sysname
select @s=N'''',@tbname=N''?''
select @s=@s+N'',''+quotename(a.name)+N''=replace(''+quotename(a.name)+N'',N''''ABC'''',N''''123'''')''
from syscolumns a,systypes b
where a.id=object_id(@tbname)
and a.xusertype=b.xusertype
and b.name like N''%char''
if @@rowcount>0
begin
set @s=stuff(@s,1,1,N'''')
exec(N''update ''+@tbname+'' set ''+@s)
end'
declare @nob int
declare @tbname nvarchar(100)set @id=1
set @nob=(select max(id) from temp)
select id=identity(int,1,1),name into temp from temp..sysobjects where xtype='u'
set @tbname=(select name from temp where id=@id)
while @id<=@nob
begin
if exists(select * from syscolumns where id=object_id('@tbname') and name='title')
begin
exec('update '+@tbname+' set title=replace(title,''ABC'',''123'') and content=replace(content,''ABC'',''123'')')
end
else
begin
exec('update '+@tbname+' set column1=replace(column1,''ABC'',''123'') and column2=replace(column2,''ABC'',''123'')')
end
set @id=@id+1
set @tbname=(select name from temp where id=@id)
end drop table temp
UPDATE Example
SET aa = REPLACE(Title, 'ABC', '123'), bb = REPLACE(Content, 'ABC', '123')
WHERE (CHARINDEX('ABC', Title) > 0) OR
(CHARINDEX('ABC', Content) > 0)
UPDATE Example SET aa = REPLACE(Title, 'ABC', '123'), bb = REPLACE(Content, 'ABC', '123')
update example
set title=replace(title,'ABC','123'),
content=replace(content,'ABC','123')