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 編輯過