CODEWITHSUNDEEP
mysql
Hammerbot
Hammerbot

Reputation: 16334

Match a mysql stored REGEX

Here is the initial problem. We have a redirections table:

+----+-------------+------------------------+------+
| id |     uri     |      redirection       | code |
+----+-------------+------------------------+------+
|  1 | ^/foo$      | https://www.google.com |  301 |
|  2 | ^/bar[0-9]$ | https://facebook.com   |  301 |
+----+-------------+------------------------+------+

Now, I'm looking to make a request with a URI and see if it matches any of the regex on this table. For example, if I look for /foo it would return the first line. If I look for /bar5, it would return the second line.

As far as my research went, it is the opposite behaviour of REGEXP that I am looking for. Is it even possible?

The only solution I came with to solve the problem was to retrieve all the lines and test each regex against my string. But this comes with some performances problems on my PHP application... Is their a Mysql way of doing this?

Upvotes: 1

Views: 27

Answers (1)

Solarflare
Solarflare

Reputation: 11106

The pattern in expr REGEXP pat does not need to be a constant, but can be your uri column:

select * from redirections where '/bar5' regexp uri;

It will have to check every row, as it is obviously not possible to calculate the pattern by looking at the matching string.

Upvotes: 1

Related Questions