Search and Replace in a TEXT column

By Bill Graziano on 18 January 2004 | Tags: Data Types

This article was written by Davide Mauri. He writes "If you ever worked with tables using the text datatype you have found that you cannot use the REPLACE TSQL function on it. Ok, that’s fine we know that the text datatype has some drawbacks, but we still need to use it (for example I cannot work without it, since a great part of my work is to build content management systems). Davide shows us a way to accomplish this using the UPDATETEXT function.

To make things a little bit more complicated (and so more close to real world problems), we also need to find and replace a text value in the entire table, not only limiting our search to a single field in a single row. Well, now that we’ve got our problem to solve, let’s see how to get out of it in a productive way: the first command we have to look for is updatetext. This command is somehow similar to replace, but has more functionalities and works only with text, ntext and image fields.

This is the command syntax:

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 } ]

It can be used to replace text specifying, beside the table and the column in which we’re going to operate, the position at which the text we have to replace begins (insert_offset), its length (delete_lenght) and then the replacing text (inserted_data). 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. To do this we’re will use a cursor:

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 where we’re performing the search and replace, which is need by the updatetext function, and the charindex function will return the starting position of the text we’re going to replace. Now that the cursor is ready to be used, we just have to define the replacing text and some variables that will hold the values given by the cursor. We also need to have the length of the text the will be removed, and we’ll to use the len function to get it:

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

In this way any row the contains the searched text will have first occurrence of that text replaced with the new value. If you have more than one occurrencies of your text, and you need all of them replaced, you just have to execute this script until the select statement bound to the cursor return 0 rows.

You can find the complete script in a template format. Just use Ctrl+Shft+M in Query Analyzer to replace the parameters.