Search code examples
sqlmysqlreplace

How can I find and replace in MySQL?

I need to change the server paths that were stored in my database (Wordpress), so I am searching for the string "/home/". Is there some kind of command such as str_replace($search, $replace, $subject) equivalent in SQL?

Edit: What if I don't know what the field name is? Well, I do, but there is more than one field name. I was just hoping for a more "global" solution like in Notepad++ where I can just find all and replace all, but it seems I can only update a certain field / table?

Solution 
  • UPDATE mytable 
   SET server_path = REPLACE(server_path,'/home/','/new_home/');

    Link to documentation.

    Edit:
    If you need to update multiple fields you can string them along—with commas in between—in that same UPDATE statement, e.g.:

    UPDATE mytable 
   SET mycol1 = REPLACE(mycol1,'/home/','/new_home/'), 
       mycol2 = REPLACE(mycol2,'/home/','/new_home/');