Search and Replace in SQL TEXT or NTEXT column -

In the past, when doing global search and replaces across text fields in SQL server, I’d been taking the path of least resistance and just writing ASP.NET pages to loop through and performing .Replace() on the SQL column in question.

Of course a devoted programmer, or at least one with more time on their hands, would have instead spent the time trying to find a better way to skin that cat. Well, courtesy of Google, I think I’ve found one. This article from SQLTeam.com all but handed me the answer. Since you can’t use the TSQL REPLACE function against a TEXT (or NTEXT) column the code below will work using UPDATETEXT instead:

/*
*
* Search & Replace 
* Use Ctrl+Shift+M to replace template values
*
*/
  
set xact_abort on
begin tran
  
declare @otxt varchar(1000)
set @otxt = '<string1, text, text to be replaced>'
  
declare @ntxt varchar(1000)
set @ntxt = '<string2, text, replacing text>'
  
declare @txtlen int
set @txtlen = len(@otxt)
  
declare @ptr binary(16)
declare @pos int
declare @<string3, text, name of Unique Row ID> int
  
declare curs cursor local fast_forward
for
select 
    <string3, text, name of Unique Row ID>,
    textptr(<field_name, sysname, target text field>),
    charindex(@otxt, <field_name, sysname, target text field>)-1
from 
    <table_name, sysname, target table> 
where 
    <field_name, sysname, target text field> 
like 
    '%' + @otxt +'%'
  
open curs
  
fetch next from curs into @<string3, text, name of Unique Row ID>, @ptr, @pos
  
while @@fetch_status = 0
begin
    print 'Text found in row id=' + cast(@<string3, text, name of Unique Row ID> as varchar) + ' at pos=' + cast(@pos as varchar)
    
    updatetext <table_name, sysname, target table> .<field_name, sysname, target text field> @ptr @pos @txtlen @ntxt
  
    fetch next from curs into @<string3, text, name of Unique Row ID>, @ptr, @pos    
end
  
close curs
deallocate curs
  
commit tran

Of course if you compare the code above to that found in the article linked at top, you’ll notice some small discrepancies. I quickly discovered that the original code assumed that the tables unique identifier was named id. This wasn’t the case for my problem at hand and I figured that if I save this out as a SQL file, the problem will come up again so I changed every instance of "id" to be "<string3, text, name of Unique Row ID>". This little snippet of code populates the "Specifiy Values for Template Parameters" screen that pops up when you press Ctrl+Shift+M from within SQL Mangler.

One other note is that his only changes the FIRST instance of the code in any single record so it is great for doing your search and replace where there is only one instance to be replaced in one or more records. You can use this code to replace multiple instances of the search string in a single record but you’ll need to run it multiple times until the record is no longer found.

Do you have a better way of doing this? If so, let me know! Post your LEGITIMATE comment using the comment area below.

Author
Recent Posts

Stalk Me...

Michael Gibbs

I'm a technologist at heart, assisting companies best utilize the Internet to market themselves and sell their products and services online. This includes organizations as large as Google and as small as one or two individuals.
At Over The Top and Exhibit A Communications, I've programmed solutions for Google as well as at least one other company that was later acquired by Google.

I've been CTO of an Internet SaaS company and spent my time pretty evenly between guiding the future technical strategy of the company, architecting software solutions for my dev teams, designing and running a data center to service our clients world-wide as well as being a technical evangalist/sales engineer to our media clients large and small.

I've also been Chief Photographer of the Daily Sun/Post newspaper back when it was a 5-day a week daily newspaper.

I also spent a great many years as a beach lifeguardfor the City of San Clemente as well as Jr. Lifeguard instructor and then as it's program coordinator.
Bragging rights
Private Pilot with Instrument rating and proud husband of soon to be 25 years (and counting).

Stalk Me...

Related