SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
The LIKE Operator
The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern |
LIKE Operator Example
The "Persons" table:
P_Id |
LastName |
FirstName |
Address |
City |
1 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
3 |
Pettersen |
Kari |
Storgt 20 |
Stavanger |
Now we want to select the persons living in a city that starts with "s" from the table above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE 's%' |
The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
The result-set will look like this:
P_Id |
LastName |
FirstName |
Address |
City |
1 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
3 |
Pettersen |
Kari |
Storgt 20 |
Stavanger |
Next, we want to select the persons living in a city that ends with an "s" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%s' |
The result-set will look like this:
P_Id |
LastName |
FirstName |
Address |
City |
1 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
Next, we want to select the persons living in a city that contains the pattern "tav" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%tav%' |
The result-set will look like this:
P_Id |
LastName |
FirstName |
Address |
City |
3 |
Pettersen |
Kari |
Storgt 20 |
Stavanger |
It is also possible to select the persons living in a city that NOT contains the pattern "tav"
from the "Persons" table, by using the NOT keyword.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City NOT LIKE '%tav%' |
The result-set will look like this:
P_Id |
LastName |
FirstName |
Address |
City |
1 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
|