Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update a column value, replacing part of a string

Tags:

sql

mysql

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?

like image 561
Addev Avatar asked Apr 16 '12 15:04

Addev


People also ask

How do you replace a part of a value in SQL?

SQL Server REPLACE() FunctionThe REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive. Tip: Also look at the STUFF() function.


2 Answers

UPDATE urls SET url = REPLACE(url, 'domain1.com/images/', 'domain2.com/otherfolder/') 
like image 198
Dmytro Shevchenko Avatar answered Sep 28 '22 04:09

Dmytro Shevchenko


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

like image 24
Marc B Avatar answered Sep 28 '22 06:09

Marc B