MySQL Reg ex, need to match phrases in any order

Question

I am using a MySQL Database to search through a list of categories. My query is:

select * from cat where name REGEXP('(region_Long Island)+(.)*(sport_Outdoor Track)');

where the values "region_Long Island" and "sport_Outdoor Track" are passed in. I need to be able to match these categories, regardless of the order they are in. In the table, it is possible to have various combinations of these two categories. I need to match any records that have both of these categories, regardless of what order they are listed in.

I am not able to change the query itself, only modify what is passed into th REGEXP function.

Thank you

Answer 1

SELECT  *
FROM    cat
WHERE   name RLIKE 'region_Long Island'
        AND name RLIKE 'sport_Outdoor Track'

Answer 2

If you can use only a single regexp and you can't change the SQL query, then to match both A and B in any order, you need a regexp that matches AB or BA:

'region_Long Island.*sport_Outdoor Track|sport_Outdoor Track.*region_Long Island'

---

Re your comment:

What about cases where there are more than two, in any particular order?

If you had patterns A, B, and C any you needed to find all three in any order, you'd need a regexp that matches ABC, ACB, CAB, CBA, BAC, or BCA. This quickly starts to look like you need n! permutations if you have n patterns.

That's why a single regular expression is not a good solution for these cases. You're going to have to use another approach.

I am not able to change the query itself, only modify what is passed into the REGEXP function.

Sorry, that's not going to work.