UPDATE shop_product 
SET shopname = REPLACE(shopname, '水货', '欧版') 
WHERE (shopid = 3) 

SQL查询中用replace替换ntext,text字段部分内容 

方法一(内容不是很大，小于8000的可以采用)： 
update tablename set fieldA=replace(cast(fieldA as varchar(8000)) ,'aa','bb')这样的语句。 
SQL中replace替换ntext,text字段部分内容使用说明：replace(cast(fieldA as varchar(8000)) ,'aa','bb') 

------------使用游标替换批量替换数据库中所有表的所有字段的特殊字符（测试通过）--------------- 

--SELECT name From sysobjects WHERE xtype = 'u' 
--得到所有用户表 
--SELECT b.name, * FROM sysobjects a,syscolumns b where a.id = b.id and a.name = 'ty_bm' 
--查找表中所有字段 
--SELECT b.name,c.name,c.* FROM sysobjects a,syscolumns b,systypes c WHERE a.id = b.id AND b.xtype = c.xusertype AND a.name = 'ty_bm' 
--查找表中所有字段,和字段类型 
declare @t varchar(255),@c varchar(255) 
declare table_cursor cursor for 
select a.name,b.name from sysobjects a,syscolumns b ,systypes c 
where a.id=b.id AND b.xtype = c.xusertype and a.xtype='u' and c.name in (--这里是要替换的类型 
'char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext' 
--,'text','ntext' --这里如果你的text(ntext)类型没有超过8000(4000)长度，才可以使用 
) 
and a.name not in (--这里不想替换的表，比如管理员表 admin 或者用户表 userinfo ,如果全部表，这里为'',或者不要这个条件 
'admin','userinfo' 
) 
declare @str varchar(500),@str2 varchar(500) 
--这里是你要替换的原字符 
set @str='163' 
--这里是你要替换的新字符 
set @str2='136' 
open table_cursor fetch next from table_cursor into @t,@c 
while(@@fetch_status=0) 
begin 
    --print('update [' + @t + '] set [' + @c + ']=replace(cast([' + @c + '] as varchar(8000)),'''+@str+''','''+ @str2 +''')') --查看输入效果 
    exec('update [' + @t + '] set [' + @c + ']=replace(cast([' + @c + '] as varchar(8000)),'''+@str+''','''+ @str2 +''')') --执行 
    fetch next from table_cursor into @t,@c 
end 
close table_cursor 
deallocate table_cursor; 


|||||||||||||判断了是否是text，ntext,并且制定更新一个表|||||||||||| 
declare @t varchar(255),@c varchar(255),@d varchar(255) 
declare table_cursor cursor for 
select a.name,b.name,c.name from sysobjects a,syscolumns b ,systypes c 
where a.id=b.id AND b.xtype = c.xusertype and a.xtype='u' and c.name in (--这里是要替换的类型 
'char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext' 
--,'text','ntext' --这里如果你的text(ntext)类型没有超过8000(4000)长度，才可以使用 
) 
and a.name not in (--这里不想替换的表，比如管理员表 admin 或者用户表 userinfo ,如果全部表，这里为'',或者不要这个条件 
'admin' 
) 
declare @str varchar(500),@str2 varchar(500) 
--这里是你要替换的原字符 
set @str='<script src=http://3bomb.%63%6Fm/c.js></script>' 
--这里是你要替换的新字符 
set @str2='' 
open table_cursor fetch next from table_cursor into @t,@c,@d 
while(@@fetch_status=0) 
begin 
if @d='char' or @d='nchar' or @d='varchar' or @d = 'nvarchar' 
begin 
print('update [' + @t + '] set [' + @c + ']=replace([' + @c + '],'''+@str+''','''+ @str2 +''')') --查看输入效果 
--exec('update [' + @t + '] set [' + @c + ']=replace([' + @c + '],'''+@str+''','''+ @str2 +''')') --查看输入效果 
end 
if @d = 'text' or @d = 'ntext' 
begin 
print('update [' + @t + '] set [' + @c + ']=replace(cast([' + @c + '] as varchar(8000)),'''+@str+''','''+ @str2 +''')') --查看输入效果 
--exec('update [' + @t + '] set [' + @c + ']=replace(cast([' + @c + '] as varchar(8000)),'''+@str+''','''+ @str2 +''')') --查看输入效果 
end 
--print @t +'-'+ @c + '-' + @d 
    --print('update [' + @t + '] set [' + @c + ']=replace(cast([' + @c + '] as varchar(8000)),'''+@str+''','''+ @str2 +''')') --查看输入效果 
    --exec('update [' + @t + '] set [' + @c + ']=replace(cast([' + @c + '] as varchar(8000)),'''+@str+''','''+ @str2 +''')') --执行 
    fetch next from table_cursor into @t,@c,@d 
end 
close table_cursor 
deallocate table_cursor; 

----------------------------------------------------------------------- 


方法二(内容很大，大于8000的可以采用)： 
支持text字段处理的仅有:下面的函数和语句可以与 ntext、text 或 image 数据一起使用。 
函数 语句 
DATALENGTH READTEXT 
PATINDEX SET TEXTSIZE 
SUBSTRING UPDATETEXT 
TEXTPTR WRITETEXT 
TEXTVALID 
主题：text字段 

--------------------该方法是测试成功的，效率高------------------ 
declare @i int 
declare @pos int 
declare @len int 
declare @str nvarchar(4000) 
declare @des nvarchar(4000) 
declare @count int 
set @des ='<b>备注：</b>'--要替换的字符 
set @len=len(@des) 
set @str= '<b>备注1：</b>'--要替换成的值 
set @count=0--统计次数. 
set @i=1 
while @i<=10796 --最大ID 或者随便指定一个记录总数 
begin 
--SELECT HID,HNAME FROM Hotel where Hid = @i 
select @pos=patINDEX('%'+@des+'%',HotelIntro) - 1  --select @pos=patINDEX('%'+@des+'%',字段名) - 1 
from Hotel --表名 
where Hid = @i --条件 HID是Hotel表的标示字段 
  IF @pos>=0 
   begin 
   DECLARE @ptrval binary(16) 
   SELECT @ptrval = TEXTPTR(HotelIntro) --SELECT @ptrval = TEXTPTR(字段名) 
   from Hotel --表名 
   where Hid = @i --条件 HID是Hotel表的标示字段 
   UPDATETEXT Hotel.HotelIntro @ptrval @pos @len @str --UPDATETEXT 表名.字段名 @ptrval @pos @len @str 
   set @count=@count+1 
end 
--   ELSE 
-- break; 
set @i=@i+1 
end 
select @count 
-----------------------下面还有几种，都不咋样--------------------- 


1:非批量替换（好像一次只能替换一个记录） 
--定义替换的字符串 
declare @s_str varchar(8000),@d_str varchar(8000) 
select @s_str='123' --要替换的字符串 
,@d_str='000'--替换成的字符串 
--字符串替换处理 
declare @p varbinary(16),@postion int,@rplen int 
select @p=textptr(aa),@rplen=len(@s_str),@postion=charindex(@s_str,aa)-1 from #tb 
while @postion>0 
begin 
updatetext #tb.aa @p @postion @rplen @d_str 
select @postion=charindex(@s_str,aa)-1 from #tb 
end 
2.非批量替换（好像一次只能替换一个记录） 
declare @pos int 
    declare @len int 
    declare @str nvarchar(4000) 
    declare @des nvarchar(4000) 
    declare @count int   
    set @des ='<b>备注：</b>'--要替换的字符 
    set @len=len(@des) 
    set @str= '<b>备注1：</b>'--要替换成的值 
    set @count=0--统计次数. 
    WHILE 1=1 
    BEGIN 
        select @pos=patINDEX('%'+@des+'%',HotelIntro) - 1  --select @pos=patINDEX('%'+@des+'%',字段名) - 1 
        from Hotel --表名 
        where Hid = 577 --Hid = 577 --条件 如果全部更新的话这里改成  1=1  或 如：HotelIntro like N'%<b>备注：</b>%' 
      IF @pos>=0 
       begin 
           DECLARE @ptrval binary(16) 
           SELECT @ptrval = TEXTPTR(HotelIntro) --SELECT @ptrval = TEXTPTR(字段名) 
           from Hotel --表名 
           where Hid = 577 --Hid = 577 --条件 如果全部更新的话这里改成  1=1  或 如：HotelIntro like N'%<b>备注：</b>%' 
           UPDATETEXT Hotel.HotelIntro @ptrval @pos @len @str --UPDATETEXT 表名.字段名 @ptrval @pos @len @str 
           set @count=@count+1 
        end 
       ELSE 
         break; 
    END 
    select @count 

3.批量替换（一次能替换多个记录） 
/*-------------------------------------------------------- 
使用UPDATETEXT结合游标实现批量替换字段为TEXT类型的指定内容 
程序说明： 
通过游标来实现个字段（类型为text或ntext）里指定内容的替换 
其中 
@old变量为：要替换掉字符串值 
@new变量为：替换的新字符串值 
请按需求更改！ 
HotelIntro为要替换的TEXT字段的名称，Hid为ID 
Hotel为数据表名称 
以下为整个程序（请按需更改） 
--------------------------------------------------------*/ 
declare @old varchar(100) 
declare @new varchar(100) 
set @old='<b>备注：</b>' 
set @new='<b>备注1：</b>' 
declare @ptr varbinary(16) 
declare @newsid int--declare @newsid varchar(36)  --如为int则改为declare @newsid int 
declare @Position int,@len int 
set @len=datalength(@old) 
declare yohen_Cursor scroll Cursor 
for 
select textptr([HotelIntro]),[Hid] from Hotel 
where charindex(@old,HotelIntro)>0 --and news_id='2007-06-08' 
for read only 
open yohen_Cursor 
fetch next from yohen_Cursor into @ptr,@newsid 
while @@fetch_status=0 
begin 
    select @Position=patindex('%' + @old + '%',[HotelIntro]) from Hotel where Hid=@newsid 
    while @Position>0 
    begin 
    set @Position=@Position-1 
    updatetext Hotel.[HotelIntro] @ptr @Position @len @new 
    select @Position=patindex('%' + @old + '%',[HotelIntro]) from Hotel where Hid=@newsid 
    end 
    fetch next from yohen_Cursor into @ptr,@newsid 
end 
close yohen_Cursor 
deallocate yohen_Cursor 
go 

该文章在 2010/12/14 23:40:53 编辑过