How to replace words in a sql database

Question

i need to replace only the domain of an URL present in many rows of a database with a different one. For example, the current URLs are something like www.myoldsite.com/index?s=something and i need to replace only the domain so they will look like www.mynewsite.com/index?s=something and so on. Is it even possible to do that in mysql?

I know how to update and set full values for all rows in a table having certain value but not only part of them with this:

UPDATE `mydatabase`.`mytable` SET `mycolumn` = 'http://mynewsite.com/' WHERE `mycolumn` = 'http://myoldsite.com/';

What can i do in this case?

Thank you.

Answer 1

Yes. Use the function replace():

UPDATE mydatabase.mytable
    SET mycolumn = replace(mycolumn, 'http://myoldsite.com', 'http://mynewsite.com/')
    WHERE `mycolumn` like 'http://myoldsite.com/%';

This isn't perfect, if the old site appears more than once in the URL.

Perhaps a better way is:

UPDATE mydatabase.mytable
    SET mycolumn = concat('http://mynewsite.com/',
                          substr(mycolumn, length('http://myoldsite.com/')
                         )
    WHERE `mycolumn` like 'http://myoldsite.com/%';

Answer 2

I think you're looking for something like

UPDATE MyTable

SET StringColumn = REPLACE (StringColumn, 'SearchForThis', 'ReplaceWithThis')

WHERE SomeOtherColumn LIKE '%PATTERN%'

Answer 3

You should use REPLACE to replace the domain when you've found matches:

UPDATE mydatabase.mytable 
SET `mycolumn` = REPLACE(mycolumn, 'http://myoldsite.com', 'http://mynewsite.com/') 
WHERE `mycolumn` LIKE 'http://myoldsite.com/%'; -- wildcard on the end

The WHERE clause may be unnecessary here, but might be good for reference

Answer 4

you need to see --> How can I use mySQL replace() to replace strings in multiple records?
so, you can update strings ...

UPDATE mydatabase.mytable SET mycolumn=REPLACE(mycolumn, 'old url', 'new url') where mycolumn='%old_url%'

you must be modified to fit the purpose.