SQL Server - using updatetext

Initially the syntax is :

UPDATETEXT { table_name.dest_column_name dest_text_ptr } 
    { NULL | insert_offset } 
    { NULL | delete_length } 
    [ WITH LOG ] 
    [ inserted_data 
        | { table_name.src_column_name src_text_ptr } ]


Since our need is to search and replace a text in the entire table, we have to cycle through each row that contains the searched text, get the position at which the text is present, and pass it to the updatetext function, along with its length and the new text the we want to insert. we can use cursor for this implementation

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 +'%'

The function textptr will return the pointer to the text field 

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