T-SQL Tutorial

SQL LIKE


The LIKE clause in SQL Server is used to search for specific patterns in a column.


LIKE syntax

The syntax for the LIKE clause is as follows:

SELECT column(s)
FROM table
WHERE column LIKE pattern;


The "pattern" is a string that can include the wildcard characters "%" and "". The "%" character matches any number of characters (including zero characters), while the "" character matches exactly one character.


LIKE examples

For example, the following query would return all rows from the "customers" table where the "last_name" column starts with the letter "S":

SELECT first_name, last_name
FROM customers
WHERE last_name LIKE 'S%';


In SQL Server you can also use square brackets to specify a range of characters in a pattern. For example:

SELECT first_name, last_name
FROM employees
WHERE last_name LIKE '[a-d]%';

This query would return all rows from the "employees" table where the "last_name" column starts with a letter between 'a' and 'd' It's important to note that the LIKE clause is case-insensitive in SQL Server. To make it case-sensitive use COLLATE.


Employee table:

EMPLOYEE_IDNAMEDEP_ID
1John21
2Samantha22
3Tom23
4James24
5Sandra24

Example 1:

Find the employee names that contain letters: am.
SELECT * FROM employee
WHERE name LIKE '%am%';


Result:

EMPLOYEE_IDNAMEDEP_ID
2Samantha22
4James24

Example 2:

Find the employee names that begin with: J.
SELECT * FROM employee
WHERE name LIKE 'J%';


Result:

EMPLOYEE_IDNAMEDEP_ID
1John21
4James24

Example 3:

Find the employee names that end with: a.
SELECT * FROM employee
WHERE name LIKE '%a';


Result:

EMPLOYEE_IDNAMEDEP_ID
2Samantha22
5Sandra24