SQL LIKE Operator

The LIKE operator is used to search for a specified pattern in the column in the WHERE clause.

LIKE Operator

The LIKE operator is used to search for a specified pattern in the column in the WHERE clause.

SQL LIKE Operator Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

Original Table (used in examples):

Persons Table:

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

LIKE Operator Examples

Example 1

Now, we want to select people from the above "Persons" table who live in cities starting with "N":

We can use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE 'N%'

Tip:"%" can be used to define wildcards (missing letters in the pattern).

Result Set:

Id LastName FirstName Address City
2 Bush George Fifth Avenue New York

Example 2

Next, we want to select people from the "Persons" table who live in cities ending with "g":

We can use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE '%g'

Result Set:

Id LastName FirstName Address City
3 Carter Thomas Changan Street Beijing

Example 3

Next, we want to select people from the "Persons" table who live in cities containing "lon":

We can use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE '%lon%'

Result Set:

Id LastName FirstName Address City
1 Adams John Oxford Street London

Example 4

By using the NOT keyword, we can select people from the "Persons" table who live in cities not containingNot Containing People in cities with "lon":

We can use the following SELECT statement:

SELECT * FROM Persons
WHERE City NOT LIKE '%lon%'

Result Set:

Id LastName FirstName Address City
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing