Search and Replace string in text columns of mySQL and SQL Server


While maintaining huge database driven websites one comes across so many occassions when its required to change some random string from columns in a database table, The most common example is when a URL has been added multiple times and that URL changes.

In order to sort out this issue its very simple in mySQL to use the replace the function and get the desired result

update tablename set tablefield= replace (tablefield, “findstring”,”replacestring”) ;


however its quite difficult to do the same in MS SQL since there is no equivalent function to do the same on a text column. One has to do this using UPDATETEXT function which requires pointer offset to the target string to be deleted. I found a very good example at this URL

http://www.sqlteam.com/article/search-and-replace-in-a-text-column

However I had two issues with this one, the first was the use of charindex, which does not work on text fields, so I had to replace it with PATINDEX and make sure that my search string had % signs around it, since PATINDEX expects a search pattern rather then string, the second issue is that if the text occurs multiple times in a post this will only sort out the first occurrence, so I had to run this multiple times until no matches were found.

My final solution is attached

searchreplace SQL

Advertisements

One thought on “Search and Replace string in text columns of mySQL and SQL Server

Leave a Reply

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