douchu2823 2015-03-06 11:08
浏览 468
已采纳

Mysql - 从字符串中提取数字

I have path field in mysql table which stors the path of the page.

It is having page id at the end like 33, 31, 121 ...

How can I write a query to get only the ids?

Path
-----
trending-architectures/architecture_detail_page/31
trending-architectures/architecture_detail_page/33
trending-architectures/architecture_detail_page/33
trending-architectures/architecture_detail_page/33
trending-architectures/architecture_detail_page/33
  • 写回答

2条回答 默认 最新

  • doushenyu2537 2015-03-06 11:24
    关注

    If the pattern is same, you can use the function substring_index()

    mysql> select substring_index('trending-architectures/architecture_detail_page/31','/',-1) as num;
    +-----+
    | num |
    +-----+
    | 31  |
    +-----+
    1 row in set (0.00 sec)
    

    So the select statement would go as

    select
    substring_index(path,'/',-1) as number 
    from table_name
    ;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?