Using SQL LIKE and IN together

Question

Is there a way to use LIKE and IN together?

I want to achieve something like this.

SELECT * FROM tablename WHERE column IN ('M510%', 'M615%', 'M515%', 'M612%');

So basically I want to be able to match the column with a bunch of different strings. Is there another way to do this with one query or will I have to loop over the array of strings I am looking for?

Answer 1

You'll need to use multiple LIKE terms, joined by OR.

Answer 2

Use the longer version of IN which is a bunch of OR.

SELECT * FROM tablename 
WHERE column LIKE 'M510%'
OR column LIKE 'M615%'
OR column LIKE 'M515%'
OR column LIKE 'M612%';

Answer 3

How about using a substring with IN.

select * from tablename where substring(column,1,4) IN ('M510,'M615','M515','M612')

Answer 4

You can do it by in one query by stringing together the individual LIKEs with ORs:

SELECT * FROM tablename
WHERE column LIKE 'M510%'
OR    column LIKE 'M615%'
OR    column LIKE 'M515%'
OR    column LIKE 'M612%';

Just be aware that things like LIKE and per-row functions don't always scale that well. If your table is likely to grow large, you may want to consider adding another column to your table to store the first four characters of the field independently.

This duplicates data but you can guarantee it stays consistent by using insert and update triggers. Then put an index on that new column and your queries become:

SELECT * FROM tablename WHERE newcolumn IN ('M510','M615','M515','M612');

This moves the cost-of-calculation to the point where it's necessary (when the data changes), not every single time you read it. In fact, you could go even further and have your new column as a boolean indicating that it was one of the four special types (if that group of specials will change infrequently). Then the query would be an even faster:

SELECT * FROM tablename WHERE is_special = 1;

This tradeoff of storage requirement for speed is a useful trick for larger databases - generally, disk space is cheap, CPU grunt is precious, and data is read far more often than written. By moving the cost-of-calculation to the write stage, you amortise the cost across all the reads.