通过游标实现对text字段的修改,修改其中某几个字符串(updatetext )
程序说明:
通过游标来实现个字段(类型为text或ntext)里指定内容的替换
其中
@otxt变量为:要替换掉字符串值
@ntxt变量为:替换的新字符串值
请按需求更改!
TargetField为该字段的名称
TargetTable为数据表名称
以下为整个程序(请按需更改)
--------------------------------------------------------*/
declare @otxt varchar(1000)
set @otxt = 'ExistingText'
declare curs cursor local fast_forward
for
select id, textptr(TargetField), charindex(@otxt, TargetField)-1
from TargetTable where TargetField like '%' + @otxt +'%'
declare @ntxt varchar(1000)
set @ntxt = 'NewText'
declare @txtlen int
set @txtlen = len(@otxt)
declare @ptr binary(16)
declare @pos int
declare @id int
Finally we can do our search and replace:
open curs
fetch next from curs into @id, @ptr, @pos
while @@fetch_status = 0
begin
updatetext TargetTable.TargetField @ptr @pos @txtlen @ntxt
fetch next from curs into @id, @ptr, @pos
end
close curs
deallocate curs