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?
First, have to check
SELECT * FROM university
WHERE course_name LIKE '%&%'
Next, have to update
UPDATE university SET course_name = REPLACE(course_name, '&', '&') WHERE id = 1
Results: Engineering & Technology => Engineering & Technology
Try using the REPLACE function:
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
Note that it is case sensitive.
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
Try this...
update [table_name] set [field_name] =
replace([field_name],'[string_to_find]','[string_to_replace]');
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...
Source: Stackoverflow.com