replace string (URL) in database field in MySQL database

Question

I am trying to change a domain name in more than one field in a database table, but am getting a bit confused as to the syntax

I need to change a table called breadcrumbs

and change

http://oldurl.com to http://newurl.com in each entry within the table.

There is of course a full URL after the domain.

The URL appears in two columns.

Answer 1

UPDATE `breadcrumbs`
SET `column` = REPLACE(`column`, 'http://oldurl.com', 'http://newurl.com');

Do this for every column in which you want to replace the string.

See String Functions: REPLACE() in the MySQL manual.

Answer 2

UPDATE breadcrumbs
SET col1 = REPLACE(col1, 'http://oldurl.com', 'http://newurl.com'),
    col2 = REPLACE(col2, 'http://oldurl.com', 'http://newurl.com')

Answer 3

update breadcrumbs
set url1 = replace(url1, 'oldurl.com', 'newurl.com'),
url2 = replace(url2, 'oldurl.com', 'newurl.com')
where url1 like 'http://oldurl.com%'
or url2 like 'http://oldurl.com%'

Answer 4

A simple way is to use two updates:

update breadcrumbs
    set url1 = concat('http://newurl.com', substr(url1, 18))
    where url1 like 'http://oldurl.com%';

update breadcrumbs
    set url2 = concat('http://newurl.com', substr(url2, 18))
    where url2 like 'http://oldurl.com%';

You can do this in one update as well:

update breadcrumbs
    set url1 = (case when url1 like 'http://oldurl.com%'
                     then concat('http://newurl.com', substr(url1, 18))
                     else url1 end),
        url2 = (case when url2 like 'http://oldurl.com%'
                     then concat('http://newurl.com', substr(url2, 18))
                     else url2 end)            
    where url1 like 'http://oldurl.com%' or
          url2 like 'http://oldurl.com%';