[SOLVED] Using REGEXP instead of AND condition

Using REGEXP instead of AND condition

I am writing a query where the user can enter a comma(,) separated value which would be used as an AND condition on a single column - lets say address - for refined search.

For example:

/**
 * Search: Gurugram, Haryana, India
 */

SELECT * FROM users
WHERE (
    address LIKE '%Gurugram%'
    AND address LIKE '%Haryana%'
    AND address LIKE '%India%'
)

Although, the query is producing the desired result as required, but I was wondering if it could be used with REGEXP instead of adding multiple AND conditions, something like:

SELECT * FROM users
WHERE address REGEXP 'Gurugram,Haryana,India'
-- OR -- 
WHERE address REGEXP 'Gurugram+Haryana+India'
-- OR --

Is it possible this way?

Solution

You can use

SELECT *
FROM users
WHERE address REGEXP '(?=.*Gurugram)(?=.*Haryana)(?=.*India)';

Note: Lookaheads are NOT supporte in MySQL 5.7