代码改变世界

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

2010-12-01 10:10  狼人:-)  阅读(307)  评论(0编辑  收藏  举报
代码
--批量替换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