Improving IN query with multiple like operators

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Improving IN query with multiple like operators

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-03-24 : 02:53:56

I have following table calles Options.

OptionId - int Identity
Option1 - varchar (30)
Option2 - varchar (30)

I want to get all rows that have a 'I' in it. I am able to get desired output.

At present I am using multiple like operators. Is there better way to get the desired output ?

--Declare table
Declare @Options table
(
OptionId int Identity(1,1),
Option1 varchar(30),
Option2 varchar(30)
)

--Insert Sample data
Insert into @Options

Select 'I','Individual' Union All
Select 'I,C','Individual and company' Union ALL
Select 'IPR','Another' Union ALL
Select 'TPR','Second' Union ALL
Select 'D,I,N','Misc'

--Desired Output
Select * from @Options where Option1 like ('I') or Option1 like ('%I,%') or Option1 like ('%,I,%')
or Option1 like ('%,I%')

Regards,

Siji
Starting Member

4 Posts

Posted - 2009-03-24 : 03:57:42

Select * from @Options where Option1 like '%I%'
is enough

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-03-24 : 04:13:27

No, that gives me IPR too. I want only rows that have single 'I' like 'I,C' or 'TRP,I'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-24 : 07:31:35

where ','+Option1+',' like '%,I,%'

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-03-24 : 08:07:15

quote:
Originally posted by sunitabeck

where ','+Option1+',' like '%,I,%'

Thanks, your solution works. But, is it efficient than query I am using? I believe its short way of writing query i posted in my post.

Regards,

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-24 : 08:59:49

Although I have no factual basis for saying this, intuitively, I think using the single like operator would be more efficient.

The way you have it written, there are no string concatenations required, but there may be up to 4 like operator invocations. Intuitively like operators seem more expensive than string concatenations.

I am always willing to be proven wrong.

Regards,
the ever so humble Sunita

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-03-24 : 09:41:28

quote:
Originally posted by sunitabeck

Although I have no factual basis for saying this, intuitively, I think using the single like operator would be more efficient.

The way you have it written, there are no string concatenations required, but there may be up to 4 like operator invocations. Intuitively like operators seem more expensive than string concatenations.

I am always willing to be proven wrong.

Regards,
the ever so humble Sunita

No intention to prove you wrong but to find efficient way to get my required output. May be some one else can provide more efficient way.

Regards,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-25 : 09:02:43

This can help you
http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources