About the author

Steven HarmanSteven Harman is a passionate developer who believes that writing great software isn't just a job, its a craft.

ASP.NET MVP

For recent posts and more about me, scroll to the bottom.

Sponsors

Subscribe

  • Subscribe to my feed. via RSS
  • Subscribe via email via email

News

devLINK Technical Conference

Jobs

Badges

  • Subtext Project
  • Support Subtext
  • HiddenNetwork.com Banner
| Home |

A Better Search and Replace for Your Database

The ability to search for and replace a substring of text is such a useful, and often necessary, bit of functionality that nearly every text editor on the planet has it built in. However, the tool we often use to hold our text based data, the ubiquitous relational database, typically has very poor support for doing search and replace out of the box.

Its so poor in fact that I’ve actually discussed this very topic, Search & Replace for your Database, before! However, as was noted in the comments to that post, my solution hack is not 100% effective. Actually, in practice I’ve found it to be quite broken. :(

A better way to replace text

As part of my search for a better way to do the database search and replace I went to my new human-powered-answer-finding-machine, Twitter! I asked my Twitter friends

Does anyone know of a tool that allows me to do a find and replace within a given column and table of a database?

Naturally I got a few smart-ass answers back... but what else would I expect. I mean these guys wouldn’t be my friends if they too weren’t snarky know-it-alls. :) Anyhow, Kevin Dente suggested taking advantage of SQL Server’s build in Replace function.

For some reason I knew I’d tried that before and it didn’t work. But I figured what the hell and gave it another shot. Surprise, surprise... it still didn’t work and SQL Server complained that

Msg 8116, Level 16, State 1
Argument data type ntext is invalid for argument 1 of replace function.

ntext... doh!

See, I knew there was a reason it didn’t work the last time I tried that... in this particular case I’m working with a ntext datatype column. So Kevin’s suggestion was perfectly valid and would have worked just fine for a binary or character (char, varchar, nvarchar, etc...) based column.

But wait... why not just cast my ntext data to an nvarchar?

Just fake it

I tried casting my ntext data to nvarchar data and to my surprise it worked! Here’s an example where I’m searching the myTable.myColumn column for the string "rob conery" and it will be replaced with "jon galloway".

UPDATE myTable
SET myColumn = Replace(Cast(myColumn AS NVARCHAR(Max)), 
    'rob conery', 
    'jon galloway')

No don’t everyone go out and start replacing Rob with Jon. :)

What others are saying.

# re: A Better Search and Replace for Your Database
Gravatar Robert G
Dec 31, 2007
Steve,

One hack/trick I used in the distant past was to link to a database table from Access. Then you open the table and Access treats it as a spreadsheet. I could then do search and replace.

Regards
# re: A Better Search and Replace for Your Database
Gravatar Haacked
Dec 31, 2007
Well that only works if none of the data in the ntext column is larger than the max size for an nvarchar.

If that's the case, why use ntext in the first place?
# re: A Better Search and Replace for Your Database
Gravatar Damien Guard
Dec 31, 2007
@Phil: NText is a legacy issue - before SQL Server 2005 NVarChar was limited to 4000 bytes and VarChar 8000 bytes.

With 2005 they now max out at 2^31-1 bytes and NText, Text and Image are deprecated and the replacement NVarChar(Max), VarChar(Max) and VarBinary(Max) should be used respectively.

[)amien
# re: A Better Search and Replace for Your Database
Gravatar Steven Harman
Dec 31, 2007
@Phil, as Damien said, as of SQL Server 2005 ntext has been deprecated and will be removed in a future version of SQL Server. There have also been a few other datatype changes in SQL Server 2005 that I only learned about while researching this issue.
# A Better Search and Replace for Your Database
Gravatar DotNetKicks.com
Dec 31, 2007
You've been kicked (a good thing) - Trackback from DotNetKicks.com
# re: A Better Search and Replace for Your Database
Gravatar RBL
Jan 03, 2008
Caveat/grain of salt dep't.: I work for Alpha Software.

But the Alpha Five database IDE supports two kinds of search and replace:

(1) Blind, where you define the search string and the replace string, and A5 blasts through the records that meet your selection criteria, and performs the search and replace. This is not unlike using "replace all" in MSFT Word or OOo Writer.

(2) Interactive, where A5 searches for each match, stops, presents a dialog that allows you to confirm or reject the replacement, then moves to the next match. This is similar to "replace | find next" in Word or Writer.

Couldn't be any easier. Works with MSFT SQL Server (or Oracle or MySQL), etc., too, which is nice.
# re: A Better Search and Replace for Your Database
Gravatar Slevdi
Jan 29, 2008
I have been using these SQL Server text manipulation tools from Igor Nikiforov for a while now with great success:

udfunctions.blogspot.com/.../...-functions_10.html

They are free.
# re: A Better Search and Replace for Your Database
Gravatar Steven Harman
Jan 29, 2008
@Slevdi, Thanks for the link to the tools... I'll have a look!
# re: A Better Search and Replace for Your Database
Gravatar Tom Pester
Apr 08, 2008
Apex SQL Edit does it.

And there is the excellent site with sql server scripts :

vyaskn.tripod.com/...server_search_and_replace.htm
Comments have been closed on this topic.