Mysql - extract number from string

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

In PHP you can do it using preg_match_all ------------------------ preg_match_all('!\d+!', $str, $matches); print_r($matches); or else you can even use preg_match_all("/[^0-9]/","",$str,$matches); print_r($matches); — phpfresher
– phpfresher, Commented Mar 6, 2015 at 11:12
All the strings are of same pattern ? i.e. 2 / and the last value after / is a number ? — Abhik Chakraborty
– Abhik Chakraborty, Commented Mar 6, 2015 at 11:21
i cannot find a single query solution for this.. stackoverflow.com/questions/978147/… See this link.. It may help you... — phpfresher
– phpfresher, Commented Mar 6, 2015 at 11:21
and can i know 1 thing... Using PHP you can do it easily in a single step, right.. Why do you want to use MySQL for this ???? — phpfresher
– phpfresher, Commented Mar 6, 2015 at 11:25

Abhik Chakraborty · Accepted Answer · 2015-03-06 11:24:39Z

2

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
;

answered Mar 6, 2015 at 11:24

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

phpfresher Over a year ago

Hii @Abhik Chakraborty... i think he even have three numbers at the end (like 121, 122, ....).. Will this query work in that case ??

Abhik Chakraborty Over a year ago

If the pattern is same it will get any number from the end.

Ali · Accepted Answer · 2015-03-06 11:15:50Z

0

I can think of two solutions, the first one utilizes explode to split the string into an array using a "/" deliemeter and then it sets the last value of the array (which is the page id) inside a variable

$path = explode('/', $path);
$page = end($path);

Other solution just replaces whatever text is before the number with nothing

$page = str_replace('trending-architectures/architecture_detail_page/', '', $path);

answered Mar 6, 2015 at 11:15

Ali

3,4364 gold badges19 silver badges31 bronze badges

1 Comment

Rakesh K Over a year ago

In php I can. I have to do this in SELECT statement of mysql.

Collectives™ on Stack Overflow

Mysql - extract number from string

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related