C# Tutorials and offshore development in India
    Tutorials   Resources   Forum   Communities   Interview   Jobs   Projects   Offshore Development    
Silverlight Tutorials | Mentor | Code Converter | Articles | Code Factory | Computer Jokes | Members | Peer Appraisal | IT Companies | Bookmarks | Revenue Sharing |


Prizes & Awards
My Profile



Active Members
TodayLast 7 Days more...
New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !






WildCards in Sql Server


Posted Date: 29 May 2008    Resource Type: Articles    Category: Databases
Posted By: Kapil Dhawan       Member Level: Gold
Rating:     Points: 10



Introduction



This article explain the concept of WildCards characters that have special meanings within SQL WHERE clauses

Before proceeding lets create a table tab_Customer with fields CustId, CustName

I have insert these values in the table above.




CustId CustName
1 Kapil Dhawan
2 Ritish
3 Pawan
4 Parteek
5 Hardeep
6 Navneet
7 Sunita
8 Manmohan Singh
9 Sonia Gandhi
10 L.P. Yadav
11 L.K.Adwani

1) WILDCARD : %


select CustName from tab_Customer where CustName like '%Singh'

Here this wildcard tell sql server to find the CustName with the word Singh, and these can be anything before Singh.

The Result is MANMOHAN SINGH


select CustName from tab_Customer where CustName like 'Singh%'

Its result will be nothing coz there is not such record in the tale having custName with Singh+anything


select CustName from tab_Customer where CustName like 'M%H'

Its result will be MANMOHAN SINGH again, as it will ask Sql Server to fetch those names having M+anything+H

Note:

• ‘%Singh’ will fetch the records with AnyWord+Singh
• ‘Singh%’ will fetch the records with Singh+AnyWord
• ‘M%H’ will fetch the records with M+AnyWord+H







2)WILDCARD IS: _ UnderScore

This will work the same way, but it will match for a single character.


select CustName from tab_Customer where CustName like '_awan'

Its result will be Pawan


3) WILDCARD : [ ]

characters specified between brackets wildcard which will match any one characters in the specified position

select CustName from
tab_Customer where CustName like
'[L I]%'

Its result will be L.P.Yadav and L.K.Advani



4)WILDCARD :*nbsp;^

Any single character not within the specified range ([^a-f]) or set ([^abcdef])

select CustName from
tab_Customer where CustName like
'[^a-p]%'


Its will search for the records having CustName not starting with A to P. so the result will be

Ritish
Sunita
Sonia Gandhi



Responses
Author: vijayavasavi narapuram    02 Jun 2008Member Level: Silver   Points : 0
Hi,
Nice article...


Author: Mahesh Raj    07 Jun 2008Member Level: Gold   Points : 1
This is very good information,Continue posting such useful articles.


Author: hairat    08 Jun 2008Member Level: Bronze   Points : 0
yes, keep posting such useful articles


Author: thebarrett27    16 Jun 2008Member Level: Bronze   Points : 0
thanks for sharing information


Author: Gaurav Agrawal    19 Jun 2008Member Level: Silver   Points : 2
Hi,
Nice article.
One most important about your article is that you have explained every wildcard with example.Examples helps us to understand more clearly.
Keep posting.
happy programming...
Regards
gaurav Agrawal


Feedbacks      
Popular Tags   What are tags ?   Search Tags  
(No tags found.)

Post Feedback


This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.
Next Resource: Views in Sql Server 2005
Previous Resource: Maximum Capacity Specifications for SQL Server 2005
Return to Discussion Resource Index
Post New Resource
Category: Databases


Post resources and earn money! 		 
Related Resources


dotNet Slackers   BizTalk Adaptors    Web Design

it help desk

Contact Us    Privacy Policy    Terms Of Use