Finding and Replacing Text in SQL Server

While cleaning up some WordPress hacks last week, I followed some documentation and noticed that MySQL has a nice function for doing a search and replace with text in a given column. All I had to do was execute the following statement:

UPDATE content set post_content = REPLACE(post_content, 'nasty hackish code', ' ');

This worked out great. I could easily update many rows, or a single one.

The other day at work, I ran into a situation where I needed to update some bad HTML tags that were sitting in a content field in one of our applications. This database was SQL Server, so I checked the documentation, and they had a REPLACE command as well. The only problem was that it didn’t work in a similar manner, it only worked with actual variables or string content.

After digging around, I discovered you can accomplish the same effect by simply wrapping up your update statement around a retrival statement that replaces the text at the same time. This is how I got mine to work:

UPDATE content
   SET content_html =
                     (
SELECT REPLACE
                             (
                              SUBSTRING(content.content_html, 1, DATALENGTH(content.content_html)), 'nbsp' , ' '
                             ) AS UpdatedContent
                        FROM content
                       WHERE content_id = 42986
                     )
WHERE content_id = 42986

Since the replace statement requires a string, we have to use the SUBSTRING command to pull out all of the text of the column in question. Its a little crazy, but hey it works.

SQL Gurus, if you have a better alternative, please share!

What are your 10 bits on the matter? I want to know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s