Do you have a need to do a search and replace on an entire column in your MySQL database?

I had to do this recently due to a domain change.  Here's the easy way:

UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");

For example, when the shell service I started moved from silenceisdefeat.com to silenceisdefeat.org, I issued the following command to change all of the links in my blog posts:

UPDATE wp_posts SET post_content = replace(post_content,"silenceisdefeat.org","silenceisdefeat.com");

You can do this via the command line, or using a tool like PHPMyAdmin by clicking on the SQL tab.  If you're running wp-cache on your WordPress blog, don't forget to clear your cache to see the change take effect.

This entry was posted on Sunday, May 3rd, 2009 at 6:02 pm and is filed under Random Thoughts, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.