Performing a case-sensitive 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?
Last Revision: October 9th, 2009 at 09:12
Short URL: http://wp.me/phEOu-1l (Tweet This!)
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!
Thanks!
Is there a way to search and replace through the entire database?
Ed
@Dave Newton:
@Ed: As far as I know there’s no single command that’ll do the job in MySQL. However, one could definitely script this with a little Perl perhaps. I’ve done this myself in the past.
[…] http://socialcmsbuzz.com/convert-import-a-drupal-6-based-website-to-wordpress-v27-20052009/ […]
A suggestion:
If you want to be on the safe side and not have something nasty happen like what happen to Pet Supplies…
First add a new column identical to the one you want to search and replace in. Second fill that column with the values from the primary one.
Now run the search and replace on your new column and if it works, remove the primary one and rename your secondary one to take its place.
Piece of cake and you won’t risk losing data
/ Dario
Thanks a lot! I thought I’d never find a solution using MySQL query only.
I almost decided to create a script using php and mysql for this problem.
Thanks again!
You’re very welcome Marc.
@Dario: Thanks for the suggestion. That’s good advice you’ve given. I appreciate it.
Is this case sensitive or an exact match? i.e. will “dog” be able to replace both “Dog” and “dog” and “doG” or will I have to handle each of those separately? Thanks for posting this by the way
That’s a good question Jason and I’m really surprised that this hasn’t been asked before.
The function is case-sensitive.
“Authored by: Ed • Comment Posted: Feb 22nd, 2009 •Permalink
Thanks!
Is there a way to search and replace through the entire database?
Ed”
If your database is small enough, you can export it to a text file, open it in text editor, do a find and replace, then update!
In fact, while I like that command and use it, doing it the way I describe ensure you have a BACKUP of your database.
When you restore you can restore to a different db, change the application’s config file or settings to point to the new db,etc. or you can simply backup the text file before you make your changes.
@steve: Great comment. You’re absolutely right, search-and-replace on a text-file dump from the DB is a very effective way to accomplish the same thing AND ensure a degree of safety. But this would only really be practical on a smaller database as you have identified. Also, the user would have to take great care not to mess up the grammar and syntax of the SQL file otherwise he/she would find it difficult to import it back into the DB.
Good tip though.
@Ed I wrote a quick script you can upload to your server and do a find and replace across an entire database. Use at your own risk! There are no real safeguards in this script. One string simply replaces another string.
http://blog.irmsgeekwork.com/2009/phpmysql-full-database-search-and-replace
@irms: Nice contribution to the thread. Thank you.
is there a way of replacing the exact words?
For example: i have the word “Jo” ‘name‘table and i want to replace it with “hello”. But ‘name‘table i also have the word “John”. I haven’t tried it but i think this will result in replacing “Jo” with “hello”, but will also replace “John” with “hellohn”.
Hi! I was surfing and found your blog post… nice! I love your blog.
Cheers! Sandra. R.
@sami: I can’t say for certain without testing this, but IIRC this search-and-replace only does whole words.
@sandrar: Thank you.
Anyway to replace one string with another in the same way you did that single word?
Yes. I think this should work: update posts set content = replace(content,‘replace this string’,‘with this one’);
Or is that not what you meant?
Thanks very much for posting up this simple SQL Query!
I have a discussion forum with about 40,000 posts — most of them containing lots of emoticons.
The problem is that I changed my site’s domain name at one point and so all of those emoticons now reference a domain name that no longer exists. That resulted in TONS of 404 errors and gaps in the posts that looked rather ugly.
So, I needed to replace every instance of oldname.com with newname.com in the posts field without wrecking the posts themselves (that would be a disaster).
Your script enabled me to do that with ease and it updated 6,486 rows with the correct domain name
I knew it could easily be done with SQL but I didn’t know how until I came across your site — thank you!
That’s really cool Dave. Thank you for letting me know it worked for you. It’s great to hear these little success stories.
All the best.
This is great but how do I replace only one instance of the word instead of every?
@Rims: Well surely, if you only have one instance of a word to replace, then your best option would be to edit that instance using whatever tool you normally use for manipulating your content?
The search-and-replace mechanism discussed here is designed for making bulk changes.