CODEWITHSUNDEEP
mysqldatabasewildcard
fizban
fizban

Reputation: 597

Match text against wildcard expressions in database?

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.

Upvotes: 3

Views: 782

Answers (1)

Quassnoi
Quassnoi

Reputation: 425271

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 '\\'

Upvotes: 3

Related Questions