Archive for May, 2009

How to Search and Replace with SQL

Sunday, May 3rd, 2009

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.