Search & Replace in MySQL tables!!

Leave a Reply

Comment as a guest.

  1. 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.

  2. 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!! 😕

  3. 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?

  4. 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?

  5. 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.

  6. 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’

  7. 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.

  8. 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!! 😉

  9. 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.

  10. 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!

  11. 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 🙂

  12. 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?

  13. 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!! 🙂

  14. This has been quite useful to read, How would I replace all empty fields with value while leaving other fields untouched?

  15. 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!! 🙂

  16. 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`

  17. that should have read:

    UPDATE ‘table’ SET `website` = ‘www.website.com’ WHERE `website` LIKE ” AND `posts`

  18. 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!! 🙂

  19. You may be able to use this syntax:

    UPDATE ‘table’ SET `website` = ‘www.website.com’ WHERE `website` IS NULL AND `posts`…

    good luck!

  20. thanks guys, I finally got the right combination:

    UPDATE users SET `website` = ‘www.example.com’ WHERE `posts` less than 2

  21. 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

Sliding Sidebar