CODEWITHSUNDEEP
mysqlsql
user41048
user41048

Reputation: 53

How to remove text after certain word in DB using query?

I have situation like in my DB I have links like 

http://www.myurl.com/url-blog/entry/camel-shadows-pic-amazing-.htmlhttps://www.testurl.com/gatoradecoupons

http://www.myurl.com/url-blog/entry/cat-mountain-at-ukraine.htmlhttps://www.testurl.com/user/212294

I want to remove the text after .html . The catch is I want to remove the text beyond htmlhttps. We can check if the URL has this and to trim rest of the text except .html

The desired output 

http://www.myurl.com/url-blog/entry/camel-shadows-pic-amazing-.html

http://www.myurl.com/url-blog/entry/cat-mountain-at-ukraine.html

There are thousands of such URLS in DB . I wonder if there's any way to remove in one go.

Upvotes: 0

Views: 634

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

One way to do this uses substring_index():

select concat(substring_index(url, '.html', 1), '.html')

If some values don't have .html, then the above will add it. For that, you can use case:

select (case when url like '%.html%'
             then concat(substring_index(url, '.html', 1), '.html')
             else url
        end)

And, once you have tested with a select you can update the value if you want:

update t
    set url = concat(substring_index(url, '.html', 1), '.html')
    where url like '%.html%';

Upvotes: 3

Related Questions