Torkil Johnsen

My personal piece of cyberspace

Search and replace in MySQL

Posted in Databases on Jul 24th, 2006

I recently had to help a friend go through a site and correct alot of wrong URLs. Here is an easy way to do search and replace in MySQL, for those of you who didn’t know.

If you have phpMyAdmin you can use the SQL-tab to run this query. If not, you can run it from the commandline as well. Here is the query I ran: (remember of course to replace table_name, column_name, find_this and replace_with_this with real data)

UPDATE table_name SET column_name = replace(column_name,"find_this","replace_with_this");

[tags]MySQL,databases,sql,query[/tags]

4 Comments
  • http://www.flashinthepan.ca Jason

    Excellent tip! This saved me from having to change 240 table rows by hand.

    Thanks,
    Jason

  • http://www.flashinthepan.ca Jason

    Excellent tip! This saved me from having to change 240 table rows by hand.

    Thanks,
    Jason

  • Anonymous

    What if I wanted to replace a just a portion of the field? For example, in table jos_components, where name=’Articles’, update just one of the parameters in the params field? I would like to change show_email_icon=1 to show_email_icon=0 for all of my Joomla installations. Apparently somebody is using this feature as a proxy for sending spam. :-(

    • http://torkiljohnsen.com Torkil Johnsen

      Just search for show_email_icon=0 and replace with show_email_icon=1 in the params field of whatever table you want to search/replace. :)

      UPDATE jos_articles SET params = replace(params,”show_email_icon=0″,”show_email_icon=1″);

Creative Commons License
This work by Torkil Johnsen is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.