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