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, 'http://the.old.site', 'http://the.new.site');
myTable is the table containing the data I want to replace,
theTextField is the exact field in which this data is located. Obviously “http://the.old.site” is the existing information, that I want to replace, and “http://the.new.site” 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!)