Substr from MySQL by mask (regex)

I have a lot of links with params in DB (Mysql 5.7), need to extract id parameter (like this - https://item.taobao.com/item.htm?id=550585067709, there can be many params, not only one...), have preg_match with regex, which I use in php -

preg_match_all('/(?|&)id=(.*?)&/', $link, $matches); 

How can I extract this id in MySQL query?

1 answer

  • answered 2018-05-16 06:05 Tim Biegeleisen

    You may use SUBSTRING_INDEX:

    -- output assume a URL of https://item.taobao.com/item.htm?id=550585067709
    SELECT
        SUBSTRING_INDEX(SUBSTRING_INDEX(url_col, '?', -1), '&', 1)
    FROM dual;
    
    id=550585067709
    

    Demo

    Note that the above solution is robust with regards to whether or not there is more than one parameter (i.e. whether or not & is present).