SQL LIKE Operator
- Previous Page SQL Top
- Next Page SQL Wildcard
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 |
- Previous Page SQL Top
- Next Page SQL Wildcard