Monday, 11 February 2013

What are SQL Wildcards Syntax and Examples

SQL Server Wildcards 
SQL wildcards can substitute for one or more characters when searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator.
The wild cards can be "%" , "_", "[abc]"... as explained below

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 :
---------------------------------------------------------------------------------------------------------------------
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


---------------------------------------------------------------------------------------------------------------------
The Wildcard '_' Usage :
---------------------------------------------------------------------------------------------------------------------
Criteria-I:
Now we use the Wildcard '_' to select Employees whose name starts with any character followed by 'arun'

Select *from EMPLOYEE E WHERE E.E_Name LIKE '_arun'

Result:
Emp_ID
E_Name
Job
Dept_ID
Salary
1002
Karun
Team Lead
2
35000
1008
Varun
Team Lead
1
33000


---------------------------------------------------------------------------------------------------------------------
The Wildcards '[abc]' and '[a-c]' Usage :
---------------------------------------------------------------------------------------------------------------------
Criteria-I:
Now we use the Wildcard '[rst]' to select Employees whose name starts with any of the characters 'R' or 'S' or 'T'.'

Select *from EMPLOYEE E WHERE E.E_Name LIKE '[RST]%'
OR
Select *from EMPLOYEE E WHERE E.E_Name LIKE '[R-T]%'

Result:
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000
1009
Smith
Team Lead
3
50000

---------------------------------------------------------------------------------------------------------------------
Criteria-II:
Now we use the Wildcard '[rst]' to select Employees whose name NOT starts with any of the characters 'R' or 'S' or 'T'.'

Select *from EMPLOYEE E WHERE E.E_Name NOT LIKE '[RST]%'

Result:
Emp_ID
E_Name
Job
Dept_ID
Salary
1002
Karun
Team Lead
2
35000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
18000
1008
Varun
Team Lead
1
33000



Thanks.,
T%PR_Like[!Like]!You


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts