MySQL REGEXP (problem with params order)

Question

Query:

SELECT * FROM (test) test_label WHERE REGEXP "AA(.*)BB" OR test_label REGEXP "BB(.*)AA"**

Database (string):

1. AA BB 100
2. AA BB 200
3. BB AA 300
4. BB CC 100
5. AA CC 300

This query returns rows: 1, 2 and 3.

How:

Combine this expression into one (but - i want it with one REGEXP but not with OR - if it is possible it must not take order of input vars and i don't must search for all possible combinations)

Now if query is "AA BB" i must find all possible cases - in this case it's only:

• AA(.)BB|BB(.)AA

but if query is "AA BB CC" it is a lot more cases.

Test cases:

1. query "AA BB" returns rows 1,2,3.
2. query "BB 300" return only row 3.
3. query "BB CC 100" return only row 4.
4. query "BB 100" return rows 1,4.

Answer 1

AA(.*)BB|BB(.*)AA

should do, although I don't understand why you would want to capture the space between AA and BB, so AA.*BB|BB.*AA would work just as well.

If the number of possible strings increases, it depends on what you want to allow as valid matches. If you want "true" combinations, i. e. all three elements of a list like AA, BB, CC must be present exactly once, in any order, then MySQL (using a POSIX ERE engine and therefore not having backreferences implemented) leaves you no choice but to spell out all possible combinations:

AA.*BB.*CC|AA.*CC.*BB|BB.*AA.*CC|BB.*CC.*AA|CC.*AA.*BB|CC.*BB.*AA

and so on for more elements.