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?
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 archive.org, 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. :) )
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? :-)
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.
Cheers Jeremy for your nice comment. Glad to be of service.
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.
I had no ideea about this ! Saves a lot of work. Thanks
@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.
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.
@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. :-)
[…] no time could be found to run the SQL command for search and replace that takes a split second (literally) to […]
this is my favourite bit of code ever, so handy!