Every once in a while, you might find that you need to find a string or value in your MySQL database and replace it with a new value throughout. For instance, let’s say you decide to move your MySQL-based Web site from one domain to another, and now all of the links you had stored in the database are broken, because they still point to your old domain.
MySQL offers an extremely simple solution to this problem. Here is a code snippet that shows you how to do so:
UPDATE table SET column = replace(column,'search','replace');
That’s all there is to it. So, let’s say that your table is called “pages” and you want to search for all instances of “www.example.com” and replace them with “www.mysite.com” in the column called “links”. This is the command you would use:
UPDATE `pages` SET `links` = replace(`links`,'www.example.com','www.mysite.com');
I hope that helps some of you out there looking for an easy way to search and replace throughout your database. Unfortunately, I don’t believe the replace() function accepts regular expressions, so you’ll have to continue to use a scripting language (like PHP) to perform those types of search and replace.