SQL Server 2005 and WildCards characters

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.