Monday, February 11, 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 genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog