LIKE Clause

The LIKE Function

The LIKE clause is used inside WHERE to search for a specific pattern in a text column. SQL provides two main wildcards for pattern processing.

Wildcards

LIKE with % at the End

Search for values that start with a specific pattern:

SELECT * FROM EMPLOYEE
WHERE NAME LIKE 'A%';

This query returns all employees whose names start with the letter "A" — such as Alia, Ana, Aaron, and so on.

LIKE with % at the Beginning

Search for values that end with a specific pattern:

SELECT * FROM EMPLOYEE
WHERE NAME LIKE '%a';

Returns employees whose names end with the letter "a" — such as Alia, Ana, Anna.

LIKE with % on Both Sides

Search for values that contain a specific pattern anywhere:

SELECT * FROM EMPLOYEE
WHERE ADDRESS LIKE '%Chicago%';

Returns all employees whose address contains the word "Chicago".

LIKE with the _ Wildcard

The underscore _ matches exactly one character.

SELECT * FROM EMPLOYEE
WHERE NAME LIKE 'A_i';

Returns names with 3 characters that start with "A" and have "i" as the third character — such as "Ali", "Ari".

Combining Wildcards

-- Names where the second letter is 'n'
SELECT * FROM EMPLOYEE
WHERE NAME LIKE '_n%';

-- Department codes that start with 'D' and end with '3'
SELECT * FROM DEPARTMENT
WHERE CODE LIKE 'D_3';

LIKE with NOT

Negate a pattern search using NOT LIKE:

SELECT * FROM EMPLOYEE
WHERE NAME NOT LIKE 'A%';

Returns all employees whose names do not start with the letter "A".

Complete Search Example

The EMPLOYEE table:

-- Employees in a city whose name contains "gar"
SELECT * FROM EMPLOYEE
WHERE CITY LIKE '%gar%';
-- Result: Aaron, Anna

-- Employees with a 5-character name
SELECT * FROM EMPLOYEE
WHERE NAME LIKE '_____';
-- Result: Aaron

-- Employees whose name has 'u' as the second letter
SELECT * FROM EMPLOYEE
WHERE NAME LIKE '_u%';
-- Result: Juan

Escape Characters

If you need to search for the % or _ characters literally, use ESCAPE:

SELECT * FROM PRODUCT
WHERE CODE LIKE '%!_%' ESCAPE '!';

This query searches for product codes that contain the _ character.

Usage Tips

1. Avoid a leading wildcard (LIKE '%abc') if possible — performance is slower because it cannot use an index
2. Use LIKE 'abc%' for pattern-prefix searches so that the index can be utilized
3. Be aware of case sensitivity, which depends on the database configuration

After understanding pattern searching with LIKE, we will learn how to sort query results using the ORDER BY clause.