Problem: How can I replace a string with another one in a field of Text or nText datatype?
Considerations: If the field was of VarChar or nVarChar type, there is no problem, 'cause in SqlServer exists a function (like in T-Sql) that do it in native mode: the "Replace" function. But this function doesn't work on Text and nText fields. So, we have to use a script builded with another function: UpdateText
Solution:
declare @OldText varchar(1000)
set @OltText= 'Old'
declare curs cursor local fast_forward
for
select
rowid,
textptr(Field),
charindex(@OldText, Field)-1
from
Table
where
Field
like
'%' + @OldText +'%'
declare @NewText varchar(1000)
set @NewText= 'New'
declare @txtlen int
set @txtlen = len(@OldText)
declare @ptr binary(16)
declare @pos int
declare @id int
open curs
fetch next from curs into @id, @ptr, @pos
while @@fetch_status = 0
begin
updatetext Table.Field @ptr @pos @txtlen @NewText
fetch next from curs into @id, @ptr, @pos
end
close curs
deallocate curs
Notes:
Table is the reference table
Field is the field where the text will be replaced
rowid is the table id (in this example it's of int type, so the @id variable are set like int, for other datatypes change the declaration of @id variable)
No comments:
Post a Comment