Find and Replace with a MySQL Query
10 Jun, 2007
1 minute read

There are times when you have a lot of data in a database (let’s say wp_posts for a Wordpress blog like Ariejan.net). When you need to find and replace certain strings, this can be a very tedious task. Find all posts containing the “needle” string and manually replace all these occurrences with “chocolate”. With about 200 posts, you can imagine how long this would take to do manually.

But, as I always say: “You’re a programmer! You should script the hell out of everything!”

So, I found this: MySQL has built-in support to find and replace! Just a simple query will do:

UPDATE wp_posts set post_body = replace(post_body, ‘needle’, ‘chocolate’);
That’s it. The entire table ‘wp_posts’ is searched and all occurrences of “needle” are replaced with “chocolate”. The query only took about a split second.

If you're seeing this message you've probably enabled some form of adblocker. I respect that.

I use this space to show recommended books on Amazon.com in relation to the post you just read. This generates a small income that allows me to buy a book every now and then.