SQL Server LIKE Operator

LIKE Operator

In SQL Server, the LIKE operator serves as a powerful tool for precisely this purpose. This operator allows users to perform wildcard-based searches, making it a versatile and efficient way to retrieve specific data from a database. Let's explore the ins and outs of the LIKE operator and its various applications in SQL Server.

LIKE Operator

The LIKE operator is primarily used within the WHERE clause of a SQL query to filter results based on specified patterns within a column. It allows users to employ two wildcard characters:
  • "%"- Represents any sequence of characters, including none.
  • "_"- Represents any single character.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Here pattern is the string pattern you want to match. It can include the wildcard characters '%' and '_' .

We'll use below syntax for creating 'Employees' table for the examples:

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    LastName NVARCHAR(50)
);

-- Insert sample data into the Employees table
INSERT INTO Employees (EmployeeID, LastName)
VALUES (1, 'Smith');

INSERT INTO Employees (EmployeeID, LastName)
VALUES (2, 'Johnson');

INSERT INTO Employees (EmployeeID, LastName)
VALUES (3, 'Williams');

INSERT INTO Employees (EmployeeID, LastName)
VALUES (4, 'Jones');

INSERT INTO Employees (EmployeeID, LastName)
VALUES (5, 'Brown');

INSERT INTO Employees (EmployeeID, LastName)
VALUES (6, 'Davis');

INSERT INTO Employees (EmployeeID, LastName)
VALUES (7, 'Miller');

INSERT INTO Employees (EmployeeID, LastName)
VALUES (8, 'Wilson');




Examples:

1. Find all employees whose last names start with 'Smi':

SELECT * FROM Employees
WHERE LastName LIKE 'Smi%';

Output- 


2. Find all employees whose last names end with 'th':

SELECT * FROM Employees
WHERE LastName LIKE '%th';

Output-


3. Find all employees whose last names have 'ar' in any position:

SELECT * FROM Employees
WHERE LastName LIKE 'Smi%';

Output-

4. Find all employees whose last names have 'mi' as the second and third letters:

SELECT * FROM Employees
WHERE LastName LIKE '_mi%';

Output-

5. Find all employees whose last names have 'e' as the third letter:

SELECT * FROM Employees
WHERE LastName LIKE '__e%';

Output-

6. Find all employees whose last names are exactly five characters long:

SELECT * FROM Employees
WHERE LastName LIKE '_____';

Output-



Conclusion

The LIKE operator in SQL Server is a fundamental tool for performing pattern matching within the database. Its flexible usage, combined with the power of wildcard characters, allows for the efficient retrieval of data that meets specific criteria. By understanding how to utilize the LIKE operator effectively, SQL Server users can streamline their data querying processes and extract valuable insights from their databases with ease.


Also Read: