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

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

图片转代码服务由CSDN问答提供 功能建议

我在mysql表中有路径字段,用于存储页面的路径。

最后有一个页面ID,如33,31,121 ......

如何编写查询以仅获取ID

 <代码>路径
 ----- 
trending架构/ architecture_detail_page / 31 
trending架构/ architecture_detail_page / 33 
trending架构/ architecture_detail_page / 33 
trending-  architecture / architecture_detail_page / 33 
trending-architectureures / 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条)

相关推荐 更多相似问题