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


I have situation like in my DB I have links like

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

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

    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