Search code examples
sqlmysqlreplace

MySQL function to update the domain of a URL column value

Let's say this table

+--------------+---------------------------------+
| id           | path                            |
+--------------+---------------------------------+
| 1            | http://old.com/img/1.png        | 
| 2            | http://old.com/img/2.png        | 
| 3            | http://old.com/img/3.png        | 
| 4            | http://old.com/img/4.png        | 
| 5            | http://old.com/img/5.png        | 
| 6            | http://old.com/img/6.png        | 
+--------------+---------------------------------+

what sentence will update it to

+--------------+---------------------------------+
| id           | path                            |
+--------------+---------------------------------+
| 1            | http://new.com/img/1.png        | 
| 2            | http://new.com/img/2.png        | 
| 3            | http://new.com/img/3.png        | 
| 4            | http://new.com/img/4.png        | 
| 5            | http://new.com/img/5.png        | 
| 6            | http://new.com/img/6.png        | 
+--------------+---------------------------------+

I don't know how to replace a string in MySQL.

"UPDATE table SET path = REPLACE(path, 'old.com', 'new.com')"??

I can't risk messing up the database here!

Solution 
  • update mytable set path = replace(path, 'old.com', 'new.com');

    If you're worried about the effect of an update (and you should be), I do this first:

    select path, replace(path, 'old.com', 'new.com') as new_path
from mytable;

    And I just eyeball the results to see if they "look OK". If they do, if fire the update.