Search This Blog & Web

Thursday, July 22, 2010

Using wildcard characters in Search feature for SQL Server

Like operator is one the built in function use for search function for different requirements. For example user wants to search all data from a table where name contains “q” in its string. User use like operator with ‘%q%’ but if anyone wants to search all data that lies between ”m” and “q” then we need to do some difference technique to get required result. SQL server uses few wildcard characters for this kind of situations.
We have following wildcard characters available
·         Percent (%)
Find any length that contain mentioned characters in the string. Like %s search sha,shamas,whats etc.
·         Underscore (_)
Find exactly one character in mentioned string. Like _ha would found sha but could not find shamas etc.
·         Square Brackets ([])
Find any range of characters mentioned between brackets like [m-q] return all data between m to q. we can also use [m,n,o,p,q]
·         Caret (^)
Any character not in mentioned range like [^m-q].
For performance issue try to avoid not in clause and like clause because it did not include indexes in search.
To understand we have following table structure and data for our example.
/****** Object:  Table [dbo].[code]    Script Date: 07/22/2010 16:34:43 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[code]') AND type in (N'U'))
DROP TABLE [dbo].[code]

CREATE TABLE [dbo].[code](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [name] [varchar](50) NULL


INSERT INTO [code] ([id],[name])VALUES(1,'this')
INSERT INTO [code] ([id],[name])VALUES(2,'that')
INSERT INTO [code] ([id],[name])VALUES(3,'Which')
INSERT INTO [code] ([id],[name])VALUES(4,'What')
INSERT INTO [code] ([id],[name])VALUES(5,'shamas')
INSERT INTO [code] ([id],[name])VALUES(6,'qamar')
INSERT INTO [code] ([id],[name])VALUES(7,'atif')
INSERT INTO [code] ([id],[name])VALUES(8,'kashif')
INSERT INTO [code] ([id],[name])VALUES(9,'this')
INSERT INTO [code] ([id],[name])VALUES(10,'that')
INSERT INTO [code] ([id],[name])VALUES(11,'Which')
INSERT INTO [code] ([id],[name])VALUES(12,'What')
INSERT INTO [code] ([id],[name])VALUES(13,'shamas')
INSERT INTO [code] ([id],[name])VALUES(14,'qamar')
INSERT INTO [code] ([id],[name])VALUES(15,'atif')
INSERT INTO [code] ([id],[name])VALUES(16,'kashif')
Now try this query
select * from code where name like '%m%'
select * from code where name like '%q%'
You can get required result for data containing “m” to “q”.
Results are:

By combining different search operator we can get required query as follows
select * from code where name like '[k-t]%'
and results are

You can view all data between k,l,m,n,o,p,q,r,s,t characters.
You can also use caret character as
select * from code where name  like '[^k-t]%'
and got this result

Summary: wildcard characters can be very use full feature in SQL server search operator.

1 comment:

Shamas Saeed said...

Thanks for the post. it helps me.

there is another example i have tried to return same result. have a look

Declare @tbl table (id int identity(1,1),val char(10))

insert into @tbl(val)
values ('jee'),('no'),('yes'),('234'),(' ˆ‰Š‹ŒŽ')

select * from @tbl where val LIKE '[^0-9a-zA-Z]%'