Blog » Find and Replace in SQL ntext Columns

Find and Replace in SQL ntext Columns

Normally, doing a search and replace in SQL is not radically difficult. You basically do an update using the replace() function. For example:

   1: Update Product

   2: Set Description = replace(Description, 'old text is this', 'new text will be this')

   3: where Description like '%old text is this%'

However, if the Description column in your table is a ntext field, the above code is going to error out. The correct way around this is to cast() the column as a maximum-sized varchar() type. So the above will now look like the following:

   1: Update Product

   2: Set Description = replace(cast(Description as varchar(8000)), 'old text is this', 'new text will be this')

   3: where Description like ('%old text is this%')

And with those simple changes, you should be rocking and rolling again. Of course the where clause is there to limit the scope of the replace() function. I’m not sure what the performance impact of this is but this isn’t the type of command you would run against your SQL data on a regular basis. Just a nifty little tool to keep in your back pocket for when you need it.

Thoughts? Please speak up with relevant comments using the comments section below.

Michael Gibbs
Stalk Me...
Latest posts by Michael Gibbs (see all)