Return a specific capture group/substring in MySQL 5 using REGEXP

Question

I'm having trouble writing the regular expression to do what I need in MySQL syntax.

I have the following value for column http_referer in a database table:

https://www.example.com?id=123&true

And I need to return the value of the query string parameter uid (in this case, 123), to plug in to another query.

`SELECT * FROM sites WHERE id = (http_referer REGEXP 'uid=([0-9]+)&?')

This is my query, that doesn't work, probably because I'm trying to pass in a PHP-style regular expression instead of one MySQL can use (however, I understand that MySQL doesn't even support capture groups, so I'm kind of stumped).

Answer 1

If you use MySQL prior to version 8 which doesn't support REGEXP_REPLACE(), you can use SUBSTRING_INDEX() to extract parts of a string:

mysql> set @a = 'some.domain?id=123&true';
mysql> select substring_index( substring_index(@a, '?id=', -1), '&', 1) as result;
+--------+
| result |
+--------+
| 123    |
+--------+

If the position of the parameter whose value you are interested in is not fixed, i.e. not always the first, or if parameters following it are optional and may not be present, it's a bit more tricky and you have to add more logic.

Answer 2

Old topic but this solution might help for people who don't have mysql version 8.

2 notes:

• You need a case set for each character. So this example is for maximum of 3 characters (0 -999).
• You can't use this solution when your string has multiple occurrences. For example id=123&secondid=456&anotherid=789.

SELECT
       CONCAT(
               CASE WHEN content LIKE '%id=?1%' THEN '1' ELSE '' END,
               CASE WHEN content LIKE '%id=?2%' THEN '2' ELSE '' END,
               CASE WHEN content LIKE '%id=?3%' THEN '3' ELSE '' END,
               CASE WHEN content LIKE '%id=?4%' THEN '4' ELSE '' END,
               CASE WHEN content LIKE '%id=?5%' THEN '5' ELSE '' END,
               CASE WHEN content LIKE '%id=?6%' THEN '6' ELSE '' END,
               CASE WHEN content LIKE '%id=?7%' THEN '7' ELSE '' END,
               CASE WHEN content LIKE '%id=?8%' THEN '8' ELSE '' END,
               CASE WHEN content LIKE '%id=?9%' THEN '9' ELSE '' END,
               CASE WHEN content LIKE '%id=?0%' THEN '0' ELSE '' END,

               CASE WHEN content LIKE '%id=?_1%' THEN '1' ELSE '' END,
               CASE WHEN content LIKE '%id=?_2%' THEN '2' ELSE '' END,
               CASE WHEN content LIKE '%id=?_3%' THEN '3' ELSE '' END,
               CASE WHEN content LIKE '%id=?_4%' THEN '4' ELSE '' END,
               CASE WHEN content LIKE '%id=?_5%' THEN '5' ELSE '' END,
               CASE WHEN content LIKE '%id=?_6%' THEN '6' ELSE '' END,
               CASE WHEN content LIKE '%id=?_7%' THEN '7' ELSE '' END,
               CASE WHEN content LIKE '%id=?_8%' THEN '8' ELSE '' END,
               CASE WHEN content LIKE '%id=?_9%' THEN '9' ELSE '' END,
               CASE WHEN content LIKE '%id=?_0%' THEN '0' ELSE '' END,

               CASE WHEN content LIKE '%id=?__1%' THEN '1' ELSE '' END,
               CASE WHEN content LIKE '%id=?__2%' THEN '2' ELSE '' END,
               CASE WHEN content LIKE '%id=?__3%' THEN '3' ELSE '' END,
               CASE WHEN content LIKE '%id=?__4%' THEN '4' ELSE '' END,
               CASE WHEN content LIKE '%id=?__5%' THEN '5' ELSE '' END,
               CASE WHEN content LIKE '%id=?__6%' THEN '6' ELSE '' END,
               CASE WHEN content LIKE '%id=?__7%' THEN '7' ELSE '' END,
               CASE WHEN content LIKE '%id=?__8%' THEN '8' ELSE '' END,
               CASE WHEN content LIKE '%id=?__9%' THEN '9' ELSE '' END,
               CASE WHEN content LIKE '%id=?__0%' THEN '0' ELSE '' END
           ) id
FROM 'page?id=123' test WHERE test LIKE '%?id=%' and p.domain_id=18