Friday, June 20, 2008

String replacement with Tex or nText fields

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: