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.

Finding dependency using SQL Server 2005

I got a requirement today that a developer needs to return all dependent procedures on a table "ABC" because he has changed a column name and need to change it on all its dependent objects.

I gave him first solution. He can use SSMS feature to find dependent objects.
Step1: to get this you can go to required table --> right click -- > click view dependcy

Step2: On clicking view dependency you can see dependent procedures and tables of mentioned table or procedure.

After some time developer came to me and thanks for this help but he wants to know some SQL for this work.

I have given him following query that retun all dependent procedures that contains its required table name or column name. This query also return those procedures which use required table in a dynamic statement.

Using first query he can get all procedures. While 2nd and 3rd query return required table or column name from mentioned database.

HOPE! THIS BLOG HELPS