How to extract id from url in a query?

Question

I have a table with a column containing many urls like this one:

https://myshop.com/lv/buitine-technika-elektronika/virtuves-iranga/virduliai/virdulys-elektrinis-virdulys-forme-fkg-147?id=22031685

I want to extract the id from the URL, but I have no idea how to do this. I could easily do this in Python later using this regex:

\?id\=(\d+)

But I'd like to have as much of data prepared in my query before going to python if that is possible. I know how to use regex in MySQL where clause, but no idea how to use it anywhere else. Is there a way to do this?

ID length can be different and there might be something else after that...

Some tasks (such as this one) are better off done in the Client. — Rick James
– Rick James, Commented Jul 9, 2019 at 22:45

Tim Biegeleisen · Accepted Answer · 2019-07-09 10:31:29Z

2

If every URL would only ever have a single id query parameter, then we can use SUBSTRING_INDEX:

SELECT SUBSTRING_INDEX(url, '?id=', -1) AS id
FROM yourTable;

Demo

For a more general solution in MySQL 8+, we can use REGEXP_SUBSTRING:

SELECT REGEXP_REPLACE(url, '^.*?.*id=([^&]+).*$', '$1') AS id
FROM yourTable;

Demo

The regex based approach can handle id appearing anywhere in the query string.

edited Jul 9, 2019 at 10:31

answered Jul 9, 2019 at 10:27

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

6 Comments

BenM Over a year ago

ant there migth be something else after that...

Tim Biegeleisen Over a year ago

@BenM Pre MySQL 8+ can't really cope with that requirement in general, assuming the id could appear anywhere in the query string. But, MySQL 8+ can using REGEXP_REPLACE.

milka1117 Over a year ago

@TimBiegeleisen I believe that regexp_replace literally makes an edit in a database which I am not allowed to do

Tim Biegeleisen Over a year ago

@milka1117 Running a SELECT query absolutely does not alter any data in your table, regardless of which function gets called.

Tim Biegeleisen Over a year ago

@Aurieli I don't think that MySQL 8's regex API supports lookbehinds (though I could be wrong).

|

Collectives™ on Stack Overflow

How to extract id from url in a query?

1 Answer 1

Demo

Demo

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related