Find and Replace with a MySQL Query

| Posted: - Last updated:
| Tagged general databases features mysql
| ~200wrds (~1min)

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.

My site is free of ads and trackers. I record privacy-respecting usage statistics with Fathom.

Was this post helpful to you? Why not ☕ Buy me a coffee or use Brave Browser to send a tip.