How to combine LIKE operator with IN clause in MySQL?

Other Questions MySQL
1

I’m working with a MySQL database and trying to simplify my query. Right now I have something that works but it’s really long and repetitive:

SELECT * FROM products p WHERE p.productName LIKE '%ABC%' OR p.productName LIKE '%DEF%' OR p.productName LIKE '%GHI%'

This gets really messy when I need to check for more patterns. I was wondering if there’s a way to combine LIKE with IN operator, something like this:

SELECT * FROM products p WHERE p.productName LIKE IN('%ABC%', '%DEF%', '%GHI%')

I’ve been searching for a solution but can’t find a direct way to do this. Is there some MySQL function or syntax I’m missing? Or maybe there’s a completely different approach I should be using for this type of pattern matching? Any help would be great!

2

MySQL doesn’t support that syntax directly, but you can get the same result with REGEXP:

SELECT * FROM products p WHERE p.productName REGEXP 'ABC|DEF|GHI'

This matches any of those patterns using the pipe character. Way cleaner than chaining OR conditions and performs well in most cases.

You could also use FIND_IN_SET for exact matches, but REGEXP works better for partial matching like yours. Just heads up - REGEXP can be slower on large datasets compared to indexed LIKE operations, so test performance if you’re dealing with tons of data.