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:

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:

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.

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...

Latest posts by Michael Gibbs (see all)

Leave a Reply