批量替换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