Using SELECT Statement with Like Operator - SQL Query
SQL Like Operator
It is used with WHERE CLAUSE to find a particular patten in a column.
Consider the following table 'Students'.
Stud_ID | Name | Phone | City | Country |
---|
1 | Alex | 654124 | Perth | Australia |
2 | Martin | 654125 | Sydney | Australia |
3 | Shruti | 910001 | Delhi | India |
4 | Jaya | 910002 | Mumbai | India |
5 | Paul | 450525 | London | England |
6 | Andrew | 450526 | London | England |
I) Write a query to display names that starts with letter 'a' from table Students.
Ans:
SELECT Name FROM Students
WHERE Name Like 'a%'
The above query will select all name that starts with 'a'.
Result:
II) Write a query to display the that ends with letter 'y' from table 'Students'.
ANS:
SELECT City FROM Students
WHERE City Like '%y'
The above query will select all cities which ends with letter 'y'.
Result:
iii) Write a query to display rows from the table 'Students', where condition is given as city column contains pattern 'lon'.
Ans:
SELECT * FROM Students
WHERE City Like '%lon'
The above query will select the names of the cities only if the letters 'lon' in sequence.
Result:
Stud_ID | Name | Phone | City | Country |
---|
5 | Paul | 450525 | London | England |
6 | Andrew | 450526 | London | England |
iv) Write a statement to select rows from table 'Students', where column city doesn't contain 'lon' pattern.
Ans:
SELECT * FROM Students
WHERE City NOT Like 'lon'
The above query will not display the rows that contain 'lon' pattern.
Result:
Stud_ID | Name | Phone | City | Country |
---|
1 | Alex | 654124 | Perth | Australia |
2 | Martin | 654125 | Sydney | Australia |
3 | Shruti | 910001 | Delhi | India |
4 | Jaya | 910002 | Mumbai | India |