Search and Replace in MySQL

I’ve come across a problem in one of my projects at work. It consists of searching and replacing data in a MySQL server. The data to be replaced is an old URL used in lots of text fields all over the place, it is the customers own site URL but since they moved, they now want all URLs to point to their new location.

Searching the web and checking up the MySQL function database returns the following useful command:

REPLACE(str, from_str, to_str)

It would in my case be used like this:

UPDATE myTable SET theTextField =
REPLACE(theTextField, '', '');

myTable is the table containing the data I want to replace, theTextField is the exact field in which this data is located. Obviously “” is the existing information, that I want to replace, and “” is the information this string should be replaced with.

Very simple, very elegant (well… if you forget about the site URL in the database in the first place…) Now all I have to do is try it out as well. (Expect more reports on the progress of this work!)

Leave a Reply

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

You are commenting using your 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