CODEWITHSUNDEEP
mysqlsqlreplace
Denis Evseev
Denis Evseev

Reputation: 1710

Replace everything before the last '/' that contains a special website in one column

I have a table with logo column, it contains urls to logos of companies from different websites. I have parsed logos from a website that has the greatest number of logos and now can't update urls to my local adress.

For example, a URL is:

http://logo-website.com/77/13/20/company-logo.png

I want to replace everything before the last / and add images/logo/ Eventually, it should look: images/logo/company-logo.png.

The update should be applied only to urls that contain 'logo-website.com'

This is my example, it seems to me I managed to get only %logo-website% rows but can't evrything replace before last '/'

UPDATE `companies` 
SET `Logo` = 'images/logo/' + SUBSTRING_INDEX(`Logo`, '//', 1)
WHERE name LIKE '%logo-website%'

Upvotes: 0

Views: 41

Answers (1)

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

With CONCAT() and SUBSTRING_INDEX() functions:

UPDATE table_name
SET url = CONCAT('images/logo/', SUBSTRING_INDEX(url, '/', -1))
WHERE url LIKE '%logo-website.com%'

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index

Upvotes: 2

Related Questions