How to Search and Replace with SQL

TOP del.icio.us digg

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.

One comment to “How to Search and Replace with SQL”

  1. Comment by Grannington III:

    Thanks for this.

Leave a Reply