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...
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 |
