EJBQL/MySQL Regexes and IN

Question

I have a list of Regexes and want to return those rows with a field which passes any regex. Is there anyway to something like the following:

SELECT * FROM Foo as f WHERE f.bar IN ("regex1","regex2");

It doesn't look like Regexes are possible at all in EJBQL so I'm guessing I have to use a native (MySQL) query.

score 2 · Accepted Answer · answered Dec 30 '10 at 13:33

2

Why not combine the regexes into one?

"(?:" + regex1 + ")|(?:" + regex2 + ")"

So if regex1 = "^.*foo(.*)bar" and regex2 = "baz(.*)frob$", you'd get

(?:^.*foo(.*)bar)|(?:baz(.*)frob$)

answered Dec 30 '10 at 13:33

Tim Pietzcker

+1 Thats also a good idea. Less complicated and probably faster than my approach. – Tomalak Dec 30 '10 at 13:35

score 1 · Answer 2 · answered Dec 30 '10 at 13:33

1

No, this is not possible. At least not the way you think it is.

Do this instead: Insert the regexes as rows into a table. Then query

SELECT 
  * 
FROM
  Foo AS f
  INNER JOIN Regexes AS re ON f.bar REGEXP re.pattern

answered Dec 30 '10 at 13:33

Tomalak

+1 - funnily, I find this less complicated than my version :) – Tim Pietzcker Dec 30 '10 at 13:38
@Tim: Well… it requires an extra table and inserting rows into it before you can start with the query. Using a single, concatenated regex requires less housekeeping. – Tomalak Dec 30 '10 at 13:44

2 Answers2