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.


Related Articles

Using the TIME data type in SQL Server 2008 (6 March 2008)

Using the DATE data type in SQL Server 2008 (6 December 2007)

Working with Time Spans and Durations in SQL Server (15 October 2007)

DATEDIFF Function Demystified (20 March 2007)

The Cost of GUIDs as Primary Keys (8 January 2005)

INF: Frequently Asked Questions - SQL Server 2000 - Table Variables (7 December 2003)

How to search for date and time values (2 May 2003)

Retrieving the GUID value of an inserted row (3 June 2002)

Other Recent Forum Posts

As I gain experience and get older, I'm working much slower, but producing better quality, but (3h)

Master DB 2019 problem (18h)

Please help, I can't login remote to MS SQL 2019 without sysadmin role (1d)

SSMS Cannot Connect to Newly Installed Instance (2017) (1d)

SQL server 2019 alwayson problem (2d)

Finding Possible Duplicates (4d)

SQL Agent Service will not start - timeout error (5d)

Adding a SQL connection to Microsoft Visual Studio (5d)

- Advertisement -