Monday, 11 February 2013

What are various Wildcards and How to use them in SQL Server

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

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

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

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

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


Result:

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

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


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

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts