Finding and Replacing Text in SQL Server

Published Sep 23, 2010 (14 years ago)
Danger icon
The last modifications of this post were around 14 years ago, some information may be outdated!

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:

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:

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!