Update a column value, replacing part of a string

I have a table with the following columns in a MySQL database

[id, url]

And the urls are like:

 http://domain1.com/images/img1.jpg

I want to update all the urls to another domain

 http://domain2.com/otherfolder/img1.jpg

keeping the name of the file as is.

What’s the query must I run?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

UPDATE urls
SET url = REPLACE(url, 'domain1.com/images/', 'domain2.com/otherfolder/')

Method 2

UPDATE yourtable
SET url = REPLACE(url, 'http://domain1.com/images/', 'http://domain2.com/otherfolder/')
WHERE url LIKE ('http://domain1.com/images/%');

relevant docs: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_replace

Method 3

Try using the REPLACE function:

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

Note that it is case sensitive.

Method 4

Try this…

update [table_name] set [field_name] = 
replace([field_name],'[string_to_find]','[string_to_replace]');

Method 5

You need the WHERE clause to replace ONLY the records that complies with the condition in the WHERE clause (as opposed to all records). You use % sign to indicate partial string: I.E.

LIKE ('...//domain1.com/images/%');

means all records that BEGIN with "...//domain1.com/images/" and have anything AFTER (that’s the % for…)

Another example:

LIKE ('%http://domain1.com/images/%')

which means all records that contains "http://domain1.com/images/"

in any part of the string…

Method 6

First, have to check

SELECT * FROM `university` WHERE course_name LIKE '%&amp%'

Next, have to update

UPDATE university
SET course_name = REPLACE(course_name, '&amp', '&') WHERE id = 1

Results: Engineering &amp Technology => Engineering & Technology

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0