批量替换ntext字段内容，@textA为要替换的字符串，@textB为替换后的字符串

-- 批量替换ntext字段内容，@textA为要替换的字符串，@textB为替换后的字符串
-- exec P_replace_TableNTEXT 'B_Goods','G_Content','GID','61.152.93.172:888','212.95.33.47'
create proc P_replace_TableNTEXT
(
@tableName varchar ( 50 ),
@ColNTEXT varchar ( 50 ),
@ColPrimaryKey varchar ( 50 ),
@textA nvarchar ( 500 ),
@textB nvarchar ( 500 )
)
as
exec ( '
declare @str varbinary(16),@id int,@position int,@len int
set @len = datalength( ''' + @textA + ''' )
declare cursor_replace scroll Cursor
for select textptr( ' + @ColNTEXT + ' ), ' + @ColPrimaryKey + ' from ' + @tableName + '
for read only
open cursor_replace
fetch next from cursor_replace into @str,@id
while @@fetch_status=0
begin
select @position=patindex( '' % ' + @textA + ' % '' , ' + @ColNTEXT + ' ) from ' + @tableName + ' where ' + @ColPrimaryKey + ' =@id
while @position>0
begin
set @position=@position-1
updatetext ' + @tableName + ' . ' + @ColNTEXT + ' @str @position @len ''' + @textB + '''
select @position=patindex( '' % ' + @textA + ' % '' , ' + @ColNTEXT + ' ) from ' + @tableName + ' where ' + @ColPrimaryKey + ' =@id
end
fetch next from cursor_replace into @str,@id
end
close cursor_replace
deallocate cursor_replace
' )
go