Is there a combination of "LIKE" and "IN" in SQL Server?

In SQL Server, the LIKE operator is commonly used for pattern matching within string columns, while the IN operator is utilized to filter data based on a set of specified values. By combining these two operators, we can create more efficient queries. This combination allows for filtering results based on both patterns and specific values, making it a powerful tool for data retrieval in SQL.

In this article, We will learn about the How to Combine SQL LIKE and IN Operator with the help of various examples and so on.

SQL Combine LIKE and IN Operator

• In SQL Server, the LIKE operator is used to search for a specified pattern within a string column, while the IN operator allows us to filter data based on a set of specified values.
  
• By combining LIKE and IN, we can create more nuanced queries that filter results based on both patterns and specific values.

Syntax to Combine SQL LIKE and IN Operators

The basic syntax for combining LIKE and IN in SQL Server is:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern
AND column_name IN (value1, value2, ...);

Examples

We will create on table employees on which we will perform various examples based on the SQL Combine LIKE and IN Operator:

 CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, employee_name, department, salary)
VALUES
    (1, 'Alice Johnson', 'Marketing', 75000.00),
    (2, 'Bob Smith', 'Sales', 68000.00),
    (3, 'Charlie Brown', 'IT', 95000.00),
    (4, 'Diana Prince', 'Marketing', 72000.00),
    (5, 'Evan Roberts', 'HR', 60000.00),
    (6, 'Fiona Clark', 'IT', 88000.00);

The employees table will look like this:

Example 1: Filtering Employees with Names Starting with ‘A’ or ‘F’ in Specific Departments

Query:

 SELECT *
FROM employees
WHERE (employee_name LIKE 'A%' OR employee_name LIKE 'F%')
AND department IN ('Marketing', 'IT');

Output:

Explanation: This query retrieves all employees whose names start with 'A' or 'F' and belong to the departments 'Marketing' or 'IT'. The parentheses around the LIKE conditions ensure that the OR condition is evaluated before the AND condition with IN.

Example 2: Filtering Employees with Names Containing ‘Charlie’ or ‘Bob’ in Specific Departments

Query:

SELECT *
FROM employees
WHERE (employee_name LIKE '%Charlie%' OR employee_name LIKE '%Bob%')
AND department IN ('IT', 'Sales');

Output:

Explanation: This query retrieves all employees whose names contain the word 'Charlie' or 'Bob' and belong to the departments 'IT' or 'Sales'. The use of parentheses ensures that the OR conditions for the LIKE pattern matching are properly grouped before applying the AND condition with IN.

Conclusion

Combining the LIKE and IN operators in SQL Server provides a powerful way to filter data based on both specific patterns and predefined categories. This versatile approach allows for complex and precise queries, enhancing the capability to retrieve relevant information efficiently.