Searching MySQL database by a Regex match (in reverse)

Question

If I have a database table that has one column that contains a regex pattern, is it possible to return rows (without systematically testing each row in turn) that a string matches?

for example, a table like this:

RowID     RegExPattern
1         foo\.$
2         bar\.$
3         baz\.$
4         (foo|bar)\.$

and an input string like this:

foo.php

will return RowIDs 1 and 4

Answer 1

If I have a database table that has one column that contains a regex pattern, is it possible to return rows [...] that a string matches?

Yes, that's possible.

SELECT RowID
FROM yourtable
WHERE 'foo.php' REGEXP RegExPattern

Note though that your regular expressions won't match. If you omit the $ then they will.

See it working online: sqlfiddle

(without systematically testing each row in turn)

Err... no. You need to test each row.