UPDATE: It appears that the .zip file I posted earlier was actually busted - I guess that's what I get for being lazy and not testing the parameterized version before putting it out there. Anyhow, Phil emailed me a version that actually works as advertised. I've updated the link at the bottom of this post, so grab the updated version from there.

Search and replace is a common (and most useful) feature that is present in even the most basic of text editors. Some of the more powerful text editors (I like notepad++) even allow you to use Regular Expressions for searching - and that really is powerful.

Wouldn't it be nice if there was a tool that would allow you to search and replace text within the fields of you database? I've wanted a tool like that forever... and I'm sure there are some great tools out there that do just that, but I've not yet found them. However, I did stumble across a very handy SQL script that will allow you to do a search and replace on a database text field... sweet!

I don't remember exactly where I found the original script, only that I originally came across it about six months ago... and then promptly forgot about it until just recently. So if you look at the code, recognize it, and happen to know where it originally came from, please leave a comment and let me know. :)

Oh, one thing I should mention is that the original script was busted, so what I've posted here is my own modified version - and it's worked for me several times.

I actually used the below script to update hyperlinks in a local Subtext database that I used for development work. Since I had pulled in some real production data to test with, I wanted to update some of they hyperlinks in the post content so that they would link back to my dev box. Take a look:

/*
* Search & Replace 
* Use Ctrl+Shift+M to replace template values
*/

set xact_abort on
begin tran

declare @otxt varchar(1000)
set @otxt = 'stevenharman.net/images/stevenharman_net/'

declare @ntxt varchar(1000)
set @ntxt = 'localhost:2732/images/localhost_2732/'

declare @txtlen int
set @txtlen = len(@otxt)

declare @ptr binary(16)
declare @pos int
declare @id int

declare curs cursor local fast_forward
for
select 
    id,
    textptr([Text]),
    charindex(@otxt, [Text])-1
from 
    subtext_Content 
where 
    [Text] 
like 
    '%' + @otxt +'%'

open curs

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

while @@fetch_status = 0
BEGIN
    IF @pos > -1
    BEGIN
        print 'Text found in row id=' + cast(@id as varchar) 
            + ' at pos=' + cast(@pos as varchar)
    
        updatetext subtext_Content.[Text] @ptr @pos @txtlen @ntxt
    END

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

close curs
deallocate curs

commit tran

Now, one thing you'll notice when running the script is that it will report back how many matches it found and replaced each time it's run. So that means you'll just need to hit F5 repeatedly until the script stops reporting matches. I realize that with just a few small tweaks I could make the script until no matches are found, but I'm being really lazy... so I'll leave that for Jon "Mr. Database" Galloway.

Oh yeah, and for your convenience I have a parameterized version that you can download below. Just drop it into Query Analyzer and hit Ctrl+Shift+M. Then fill in your database table, column name, search string, replace string, and run.

Get the script here: [Download]