SQL LIKE Operator

LIKE Operator

SQL LIKE operator is used to find the specified pattern in a column. LIKE operator is used with WHERE clause and searches for regular characters or wildcard characters, wildcard characters are %, [ ], _, ^.

Syntax

SELECT * FROM TableName

WHERE columnName LIKE pattern

We will take few examples from Student table.

StudentTable

Here we will try to find FirstName starting with character ‘D’ from Student table. Query output is shown in below figure.

SELECT * FROM Student
WHERE FirstName LIKE 'D%'

LIKE

You can use % wildcard on both side to search for specific character set as shown in below query. Here we are trying to find all rows from FirstName column which includes letter ‘ob’ so we got four such rows.

SELECT * FROM Student 
WHERE FirstName LIKE '%ob%'

LIKE1

If you want to find any single character within the specified range then you can use wildcard ‘[ ]’ as described below.

SELECT * FROM Student 
WHERE MiddleName LIKE '[A-J]'

LIKE2

We have searched those records where MiddleName character is between characters ‘A’ and ‘J’  so it has reverted six records as shown above.

If you have a requirement to find character at particular position then you can find those rows by underscore ‘_’ as shown in below query.

SELECT * FROM Student 
WHERE FirstName LIKE '_i%'

LIKE3

In above query we have searched FirstName column whose second character starts with letter ‘i’ so query output reverted four such records.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

CLICK HERE to watch live practical.

You may also like...

Leave a Reply

Your email address will not be published.