Match text against wildcard expressions in database?

Question

Given an input string, I'd like to return the rows from a (MySQL) database that contain a wildcard expression that would match the string. For example, if I have a table containing these wildcard expressions:

foo.*
foo.bar.*
erg.foo.*

and my input string is "foo.bar.baz", then I should get the rows "foo.*" and "foo.bar.*".

Any ideas on how this can be implemented? I'm hoping it can be accomplished with a query, but I might have to select all relevant rows and do the matching in the application.

Answer 1

SELECT  *
FROM    mytable
WHERE   'foo.bar.baz' RLIKE match

You are not limited to the wildcards and can use any regular expression MySQL supports.

However, if you only want to process asterisk wildcards, you may use this syntax:

SELECT  *
FROM    mytable
WHERE   'foo.bar.baz' LIKE REPLACE(REPLACE(REPLACE(REPLACE('\\', '\\\\'), '.', '\\.'), '%', '\\%'), '*', '%') ESCAPE '\\'