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]

GO

CREATE TABLE [dbo].[code](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [name] [varchar](50) NULL

) ON [PRIMARY]

GO

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')

GO

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.