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.