问题描述:将数据库里所有表(用户表)的某一字段值的某个字,改为另外一个字;
解决方法:使用Replace函数即可;外加游标实现;-- mssql 2005试验通过
最新版, 只需更改一处即可;
declare @oldstr varchar(100)
set @oldstr= '里' --原字符
declare @newstr varchar(100)
set @newstr= '裏' --新字符
declare @s varchar(8000)
declare tb cursor local for
select s= 'if exists(select 1 from ['+b.name+ '] where ['+a.name+ '] like ''%'+@oldstr+ '%'' )
update ['+b.name+ '] set ['+a.name+ ']= Replace('+a.name+','''+@oldstr+''','''+@newstr+''')'+ ' where ['+a.name+ '] like ''%'+@oldstr+ '%'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype= 'U' and a.status> =0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
print @s
fetch next from tb into @s
end
close tb
deallocate tb
---------------------------------------------------------------------------------
要更改两处;
declare @oldstr varchar(100)
set @oldstr= '恆' --原字符
declare @newstr varchar(100)
set @newstr= '恒' --新字符
declare @s varchar(8000)
declare tb cursor local for
select s= 'if exists(select 1 from ['+b.name+ '] where ['+a.name+ '] like ''%'+@oldstr+ '%'' )
update ['+b.name+ '] set ['+a.name+ ']= Replace('+a.name+',''恆'',''恒'')'+ ' where ['+a.name+ '] like ''%'+@oldstr+ '%'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype= 'U' and a.status> =0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
print @s
fetch next from tb into @s
end
close tb
deallocate tb