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).

[0-9+] means match a single character which is either a digit 0 to 9 or the plus sign. You probably want to move the quantifier (+) outside the character group: [0-9]+. Also WHERE id = (...) doesn't look right. — marekful
– marekful, Commented Jul 10, 2019 at 15:23
@marekful I'm using MySql 5.5 apparently, which doesn't support anything but REGEXP — mpdc
– mpdc, Commented Jul 10, 2019 at 15:48

marekful · Accepted Answer · 2019-07-10 16:24:52Z

5

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.

edited Jul 10, 2019 at 16:24

answered Jul 10, 2019 at 16:09

marekful

15.4k6 gold badges39 silver badges63 bronze badges

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

Comments

Michellos · Accepted Answer · 2021-04-20 08:29:05Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related