SQL LIKE Operator
The LIKE operator is used in
a WHERE clause to search for a specified pattern in a column.
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
Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Navis
|
Anto
|
Madras
|
TVL
|
2
|
Ji
|
Bala
|
Bombay
|
TVL
|
3
|
Christopher
|
Franklin
|
America
|
KK
|
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:
Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Navis
|
Anto
|
Madras
|
TVL
|
2
|
Ji
|
Bala
|
Bombay
|
TVL
|
3
|
Christopher
|
Franklin
|
America
|
KK
|
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:
Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Navis
|
Anto
|
Madras
|
TVL
|
2
|
Ji
|
Bala
|
Bombay
|
TVL
|
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:
Id
|
LastName
|
FirstName
|
Address
|
City
|
3
|
Christopher
|
Franklin
|
America
|
KK
|
It is also possible to select the persons living in a city that does NOT contain 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:
Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Navis
|
Anto
|
Madras
|
TVL
|
2
|
Ji
|
Bala
|
Bombay
|
TVL
|
0 comments:
Post a Comment