The Webmaster’s Weekly #12 is out & the Quick Tip tells you about a simple way of doing a search-replace in a MySQL table.
Well, it might be something new for Chrispian but I’ve been doing this for ages, infact I don’t know of any other search-replace technique for MySQL when you don’t need to replace the data of the whole column of a record, but just some text from between it, like in “hello world, today is wednesday” you want to replace “today is wednesday” with “yesterday was thursday”. This is ofcourse quite a simple example which can be done with a simple SQL. The tip however says that its quite simple to just download the data as SQL using phpMyAdmin, open it in a text editor, search-replace and then upload back after saving.
But being someone who’s done this way too many times & still does it, I wouldn’t recommend doing it to anyone unless the data you’ll be downloading is not too much and if there’s no easy way of doing it via an SQL UPDATE. The reason is why I prefer SQL in this case is because the data I work with, in which I’ve to perform search-replace, is always more than 10MB usually. So ofcourse downloading is a problem & then uploading is also a problem as I’ll have to split it into files each weighing less than 2MB(phpMyAdmin won’t upload files bigger than that, its the default max size allowed by PHP). 🙁 Quite a headache really!! :dizzy: On the otherhand, doing an SQL won’t take much time, and even writing the SQL would take up a bit of less time than downloading & uploading, and the additional benefit is that you get more fluent in SQL, so the next time it’ll be easier to do this. 😉
This is why, I wouldn’t recommend doing this download-search-replace-upload technique to anyone if using an SQL UPDATE is an option!! 😀
To search and replace in MySQL –
you can also use this syntax :
UPDATE `table_name` SET field_name=replace(field_name,’search_text’,’replace_text’);
In an whole table, you can replace sections of text within single fields using this type of query.
I don’t know what I’m missing, but how does your query differ from:
UPDATE `table_name` SET `field_name`=’replace_text’ WHERE `field_name`=’search_text’;
care to explain? It seems that your SQL is similar to mine!! 😕
There’s a significant difference.
The query: “UPDATE `table_name` SET `field_name`=’replace_text’ WHERE `field_name`=’search_text’;”
is a standard SQL UPDATE query.
The query I mentioned :
“UPDATE `table_name` SET field_name=replace(field_name,’search_text’,’replace_text’);”
Uses a built-in MySQL string function to actually search within each field’s text and replace the text specified.
The main difference is you don’t need to specify the complete field text (‘search_text’) to do the replace (‘replace_text’).
You only need to specify the text you want to search for and the replacement text, not the whole field text.
The function will search and replace the text you wish to replace within each field’s data.
do you see?
so in other words, this is just like str_replace() in PHP & Replace() in VBScript? That if my field has “strawberry is pretty cool”, I can make it “strawberries are pretty cool” by doing
UPDATE `table_name` SET field_name=replace(field_name,’strawberry is’,’strawberries are’);
Is that right?
exactly. You got it! 🙂
Given table_name and field_name, that query will search and replace every instance of “strawberry is” with “strawberries are” in each row for the given field.
its cool!! thanks!! 😉
Here’s more options –
Hello,
Instead of a “replace” statement, you can try to use a query, similar to the following:
UPDATE `tiki_articles` SET topicId=4 where topicID > 8
The second query would be:
UPDATE `tiki_articles` SET topicId=’articles & essays’ where topicID is NULL
You can check the MySQL documentation at http://dev.mysql.com/doc/
———————————
use this one
———————————-
UPDATE `tiki_articles` SET type=’Politics and Laws’ where topicID=’Politics’
Thanks for pointing out a standard UPDATE query.
The whole point of the comment was to show a way of updating a field without changing ALL the data in the field. The replace function allows you to update specific sections within a field of data.
Try doing that with an UPDATE statement and no replace function.
well, I don’t think that I need to say anything more!! I think that CitySites didn’t read the whole post & comments & jumped to conclusion that we were overdoing the simple UPDATE query!! 😉
Yep, I know the intentions were good. When it comes to computers, there’s always more than one way of doing things.
Anyway, all the best with your work and have a great New Year 2006!
kind regards,
David Thomas.
Thanks David, Happy New Year to you as well!! 🙂
Can someone tell me how to use the update query for the entire database instead of just for a particular table?
In other words, in every table in the db, I want to change the field, client_id, from 10 to 20. There are about 6 tables where the client_id is found.
I tried this, but it didn’t work:
update set client_id = REPLACE(client_id, ’10’, ’20’)
I was hoping by leaving out the table_name, it would replace across the entire db, but instead I got an error 🙁
Any help would be appreciated!
Thanks and Happy New Year to all!
You’ll need to run the SQL query for each table.
It’s possible to write a PHP script to loop through and update all the tables at once but 6 individual SQL statements is probably easiest in your case.
Because you want to change the entire field (correct?),client_id, from 10 to 20 you don’t need to use the replace() function. A normal UPDATE query will do.
If you only want to update a part of the field data, then use the replace() function.
Try this
UPDATE tablename SET client_id = 20 WHERE client_id=10;
If you use PHPMyAdmin, you can write six queries like that, changing the tablename for each one, input them all into the SQL box and run them all consecutively.
hope it’s not too complicated!
Happy New Year to you 🙂
Hi
If i have in my database about 300 image names XXXX.bmp and i want to change tham all to XXXX.jpg How can I do that?
Tommy, if you just go through the comments here, I think that you’ll be able to do that quite easily, its all been explained here!! 🙂
This has been quite useful to read, How would I replace all empty fields with value while leaving other fields untouched?
Dale, to update an empty field with value, you just need to run an ordinary SQL query, no need to use the replace() function discussed here!! 🙂
I’m trying to do something similar, I want to update a the field where there is no data, with a URL.
UPDATE ‘table’ SET `website` = ‘www.website.com’ WHERE `website` LIKE ” AND `posts`
that should have read:
🙁 it is leaving off the last part, posts less than 2
Stuka, what you are doing is not exactly search and replace!! you will have to run a simple UPDATE query to do that & instead of using the ‘LIKE’ operator, use the ‘=’ operator since you want to update fields where they are empty!! 🙂
You may be able to use this syntax:
good luck!
thanks guys, I finally got the right combination:
Hi
Since this seems to be “the” topic on search & replace, I figured I could ask for some help… Big thx in advance…
For a reason I don’t know, some administrator (I don’t know either) put a stupid collation (swedish 🙂 in my old database ; trying to export it, all accentuation (somewhat usuful in french) are now micoded, eg : é for é, etc.
I tried to use David query like this
UPDATE `table_name` SET field_name=replace(field_name,’é’,’é’);
(with propos table & field names)
but I’ve got an error returned.
So, anybody knows a way this could work ? Maybe it’s because I’m trying to replace characters and not sigle words or phrases ?
Any help’d greatly appreciated ! cheers
Finally, it works ! Sorry for interrupting.