Search and Replace in a TEXT column

By Bill Graziano on 18 January 2004 | 17 Comments | 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
	charindex(@otxt, TargetField)-1
	'%' + @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
	updatetext TargetTable.TargetField @ptr @pos @txtlen @ntxt

	fetch next from curs into @id, @ptr, @pos	

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.

Discuss this article: 17 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

How do I repair a "suspect" SQL 2005 database (5 Replies)

Parse @@VERSION (16 Replies)

Fiscal Year YTD and previous YTD (0 Replies)

How to recover dbf file created in dbf VFP databas (2 Replies)

How to recover data from corrupt SQL database? (6 Replies)

Corrupted DBF file (4 Replies)

NTEXT (3 Replies)

Change Tracking & last modified date (6 Replies)

Subscribe to

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers. Articles via RSS Weblog via RSS

- Advertisement -