Combining LIKE and IN for Advanced SQL Queries

In SQL, the LIKE operator is often used for pattern matching, while the IN operator allows us to match a value against a list of specific values. While these operators serve different purposes, it's possible to combine them to create more powerful queries.

Let's consider the following scenario: you have a table with a column named "company_id" and you want to select all rows where the "company_id" begins with specific strings, such as "M510", "M615", "M515", and "M612".

One approach might be to use the LIKE operator as follows:

SELECT * FROM tablename WHERE company_id LIKE 'M510%'

However, this will only match rows that start with "M510" exactly. To match multiple strings, you would have to create multiple queries.

Using Substring with IN

A more efficient solution is to use a substring with the IN operator. The SUBSTRING() function extracts a specified number of characters from a string, starting from a given position.

For our scenario, we can use the following query:

SELECT * FROM tablename
WHERE SUBSTRING(company_id, 1, 4) IN ('M510', 'M615', 'M515', 'M612')

This query will extract the first 4 characters from the "company_id" column and check if it exists in the list of values specified in the IN clause. By combining the substring with IN, we can achieve the desired pattern matching without having to loop over an array of strings.