Mysql - 从字符串中提取数字

Question

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

Answer 1

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
;