Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

SQL Server 2005 and WildCards characters

Author: Babar
Download Source Code : 702_SQL Server 2005 WildCards Character.zip

This article eloborate about WildCards characters that have special meanings within SQL WHERE clauses, and SQL supports several wildcard types.

The most frequently used wildcard is the percent sign (%).

Within a search string, % means match any number of occurrences of any character.

 

Before we proceed it further lets create a table Dev_Products having two columns.

 

Create Table Dev_Products

(

      Prod_ID           Int,

      Prod_Name   Varchar(255)

)

 

Inserting some values.

 

Insert Into Dev_Products (Prod_ID,Prod_Name)Values(1,'DevProd1')

Go

Insert Into Dev_Products(Prod_ID,Prod_Name)Values(2,'DevProd2')

Go

Insert Into Dev_Products(Prod_ID,Prod_Name)Values(3,'Plaza101')

Go

Insert Into Dev_Products(Prod_ID,Prod_Name)Values(4,'Coupon Park')

GO

Insert Into Dev_Products(Prod_ID,Prod_Name)Values(5,'Imate KJam')

 

For example, to find all products that start with the word ‘dev’, you can issue the following SELECT statement

 

 

SELECT

      Prod_ID,

      Prod_Name

FROM

      Dev_Products

WHERE

      Prod_name LIKE 'Dev%'

Output

 

Prod_ID     Prod_Name
----------------------
1               DevProd1
2               DevProd2

(2 row(s) affected) 

 

 

The % tells Sql Server to accept any characters values after the word “dev”.

Wildcard can be used first or middle or any part of the search pattern.

 

A rare case of wildcard is in the middle.

 

SELECT

      Prod_ID,

      Prod_Name

FROM

      Dev_Products

WHERE

      Prod_name LIKE 'D%1'

 

 

Prod_ID     Prod_Name
-------------------------------------------------------
1              DevProd1

 

 

Mean search all the products which start from D and end with d.

 

The Underscore (_) Wildcard

 

Another useful wildcard is underscore, it works same way like % but it will match only a single charater.

 

For Example

 

SELECT

      Prod_ID,

      Prod_Name

FROM

      Dev_Products

WHERE

      Prod_name LIKE '_oupon%'

 

Output

 

Prod_ID     Prod_Name
--------------------------
4               Coupon Park

 

(1 row(s) affected)

 

The Brackets ([ ]) Wildcard

 

The set of characters specified between brackets wildcard which will match any one characters in the specified position (the location of the wildcard).

 

 

SELECT

      Prod_ID,

      Prod_Name

FROM

      Dev_Products

WHERE

      Prod_name LIKE '[p1]%'

 

Output

 

Prod_ID     Prod_Name
-------------------------------
3           Plaza101

 

Brackets [] wildcard will match the charaters begins with P and end with 1.

 

The Brackets (^) Wildcard

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

 

SELECT

      Prod_ID,

      Prod_Name

FROM

      Dev_Products

WHERE

      Prod_name LIKE '[^Dev]%'

 

Output

 

Prod_ID     Prod_Name
----------- ------------------------
3           Plaza101
4           Coupon Park
5           Imate KJam

(3 row(s) affected)

Any charaters started with “Dev” will be excluded.

Article Comments
This is good

Posted on 3/13/2008 1:21:37 AM by Manish Kumar

Hello Babar
i read ur article. too good. But i have a problem with my query.

In my query i search a string inside a field in a table
Search string is "a+[B-C]"
So i used "Fieldname like '%a+[B-C]%'" but it a produce an error. can u able to help me regarding this asap.the error message is

Error in Like operator: the string pattern '%spGetForm[Row].sql' is invalid.

Posted on 6/16/2008 4:12:20 AM by Sathak Musthafa

Hi
really it is very nice article it has solved my all search related problems

Posted on 11/10/2009 1:45:25 AM by Smriti

This is one of the good artical for learning wildCard.thanks a lot

Posted on 5/18/2010 6:45:40 AM by Muhammad Imran Khan Marwat

Really good article!!

Posted on 6/11/2010 9:21:33 PM by him

First I would have to appreciate your efforts. As I have a doubt since so many days that using LIKE will reduce the performance of query..Is it right???If yes then suggest any more opearators instead of LIKE. Thanks in advance...

Posted on 6/20/2010 9:35:43 AM by Nag

very nice article

Posted on 8/10/2010 5:33:07 AM by amit singh

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How we can Check Table Size in our database.

Disclaimer - Privacy
© 2002-2017 DevASP.net