Ggtcf

I have a link like the following

https://www.example.com/xy-pz-p123456789.html

From this link I would like to extract the number '123456789' using mysql query.

I have the following query to extract everything after the last -p

SELECT id, RIGHT(`internal_id`,LOCATE('p',reverse(`internal_id`)) - 1) from pricing_dm where `id` = 1

The query returns: 123456789.html

I have the following query to get everything left of ".html"

SELECT id, left(`internal_id`,LOCATE('.html',`internal_id`) - 1) from pricing_dm where `id` = 1

The query returns https://www.example.com/xy-pz-p123456789

Is there a way how I can combine these two in one query to only extract the number.

Thank you a lot.

You can do it with a Query like this:

SELECT SUBSTRING_INDEX(
 SUBSTRING_INDEX('https://www.example.com/xy-pz-p123456789.html', '-p', -1), '.' ,1);

Sample

MariaDB [(none)]> SELECT SUBSTRING_INDEX(
 -> SUBSTRING_INDEX('https://www.example.com/xy-pz-p123456789.html', '-p', -1), '.' ,1);
+-------------------------------------------------------------------------------------------------------+
| SUBSTRING_INDEX(
 SUBSTRING_INDEX('https://www.example.com/xy-pz-p123456789.html', '-p', -1), '.' ,1) |
+-------------------------------------------------------------------------------------------------------+
| 123456789 |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> 

My suggestion would be to use a regular expression

https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Something like mysql> SELECT REGEXP_SUBSTR('internal_id', '(.*)d*(.html)'); (.)d.html

You can use MySql casting trick:

https://www.db-fiddle.com/f/oC7zRWks24b4QCS4B3RoA1/0

SELECT RIGHT(`internal_id`,LOCATE('p',reverse(`internal_id`)) - 1)+0
FROM pricing_dm