Monday, 11 February 2013

SQL LIKE Operator Syntax and Examples

SQL LIKE Operator
The LIKE operator is used to search for a specified pattern in a column.

Syntax:
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

Example :
Suppose we have an Employee Table as follows :

Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
Karun
Team Lead
2
35000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
18000
1008
Varun
Team Lead
1
33000
1009
Smith
Team Lead
3
50000

---------------------------------------------------------------------------------------------------------------------
The Wild Card '%' Usage with LIKE :
---------------------------------------------------------------------------------------------------------------------
Criteria-I:
To select Employees whose name Starts with Ravi

Select *from EMPLOYEE E WHERE E.E_Name LIKE 'ravi%'

The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

Result:

Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1003
Ravi Kumar Rao
Analyst
1
10000

---------------------------------------------------------------------------------------------------------------------
Criteria-II:
To select Employees whose name Ends with Reddy

Select *from EMPLOYEE E WHERE E.E_Name LIKE '%reddy'

Result:

Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1007
Vikram Teja Reddy
Analyst
2
18000

---------------------------------------------------------------------------------------------------------------------
Criteria-III:
To select Employees whose name contains 'Kumar'

Select *from EMPLOYEE E WHERE E.E_Name LIKE '%kumar%'

Result:

Emp_ID
E_Name
Job
Dept_ID
Salary
1003
Ravi Kumar Rao
Analyst
1
10000
1005
Sai Kumar Teja
Analyst
2
15000

---------------------------------------------------------------------------------------------------------------------
Criteria-IV:
To select Employees whose name NOT contains 'Reddy'

Select *from EMPLOYEE E WHERE E.E_Name NOT LIKE '%reddy%'


Result:


Emp_ID
E_Name
Job
Dept_ID
Salary
1002
Karun
Team Lead
2
35000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1008
Varun
Team Lead
1
33000
1009
Smith
Team Lead
3
50000

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts