Storing regular expressions in MySQL database table and maching against them

Question

I have a very interesting task, which I don't know how to implement.

I need to store many regular expressions in a database table, and need to be able to find which of them matches the given string.

For example:

id | regexp
---|-------------
1  | ^hello world$
2  | ^I have [0-9] flowers&
3  | ^some other regexp$ 
4  | ^and another (one|regexp)$

And I need to find which of those expressions matches string "I have 5 flowers". Of course I can SELECT * FROM table and loop through an expressions matching them one by one in PHP, but this would be horrible for server to handle.

Can I somehow index this table or use a special SQL query to handle this task?

I'll appreciate any answer. Thank you.

Answer 1

select * from table where $your_string RLIKE regexp 

mysql regular expressions

Answer 2

SELECT * FROM table WHERE 'some stuff' REGEXP `regexp`;

Unfortunately there is no way to use indexes with queries that use regexps.