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_IDNamePhoneCityCountry
1Alex654124PerthAustralia
2Martin654125SydneyAustralia
3Shruti910001DelhiIndia
4Jaya910002MumbaiIndia
5Paul450525LondonEngland
6Andrew450526LondonEngland

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:

Name
Alex
Andrew

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:

City
Sydney

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_IDNamePhoneCityCountry
5Paul450525LondonEngland
6Andrew450526LondonEngland

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_IDNamePhoneCityCountry
1Alex654124PerthAustralia
2Martin654125SydneyAustralia
3Shruti910001DelhiIndia
4Jaya910002MumbaiIndia