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

Question

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. Please help


Show source
| database   | mysql   | sql   | mysqli   | phpmyadmin   2016-12-31 18:12 1 Answers

Answers ( 1 )

  1. 2016-12-31 18:12

    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%';
    
◀ Go back