MySQL Search & Replace

Performing a search-and-replace through a table is easy with MySQL when you know how.
update table_name set table_field = replace(table_field,'replace_that','with_this');
So, for example, let’s say you have a table called “posts” with a data stored in a field called “content” and you want to replace all instances of the word “dog” with “cat”, then your SQL would look like this:
update posts set content = replace(content,'dog','cat');
How cool is that?

11 Reader Comments for “MySQL Search & Replace”

  1. Hey, thanks for putting this back up. I use this tidbit of code every few months - infrequently enough that I never remember the exact syntax when I need it, but often enough that living without it would be a hassle. I know I should just save the snippet on my drive somewhere… but I’d never be able to find it again; it’s much easier to search my bookmarks for ‘urbanmainframe’.

    (In the time after you remodelled the site but before you readded this post, I actually found it at, using the old link, simply because I had been too stupid to save a copy. One of these days, I’ll do that. In the meantime… thanks. :) )

  2. No problem at all Claude. I’m glad the information is useful to you. I had to repost this page as it seems to have been the most requested page from the old site - and I got sick of seeing all those 404 errors in my logs. “MySQL search & replace” is also the most popular search query that brings new readers to the site.

    In a way it’s kind of disheartening that, of all the posts I previously published, only one seems to have been appreciated! Oh well, perhaps I’ll have more luck this time round eh? :-)

  3. I’m so thankful that you did put the page back up! I have the same issue that Claude has, in that I use it so infrequently, that I can never remember it.

    I hope other visitors see that there’s also some pretty good content, as well as design going on here.

  4. Cheers Jeremy for your nice comment. Glad to be of service.

  5. I did that with my wordpress database and wrecked it. It changed all the content of every field to ‘post_content’ I don’t understand what went wrong. I don’t have a recent backup either, I have an outdated one which will have to do. Sucks.

  6. I had no ideea about this ! Saves a lot of work. Thanks

  7. @Pet Supplies: I have no idea why this didn’t work for you. It works for me, everytime I use it and others have reported success.

    @Octav: You’re welcome.

  8. Thank you. I have had a horrible week as I lost my domain name through some weird situation. After registering something new, I was not looking forward to fixing all the references to the former domain name (I use absolute paths). I found this post and within a second five years of blogging was updated.

    Thank you.

  9. @RDOwens: Wow that’s great, it’s really gratifying to know that this little post helped you out like that. Thank you for letting me know. :-)

  10. […] no time could be found to run the SQL command for search and replace that takes a split second (literally) to […]

  11. this is my favourite bit of code ever, so handy!

Leave a Comment