IN clause with strings - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

IN clause with strings

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-03-24 : 06:20:33

Hello all,
how can I make this query run properly?

-- The database is Pubs

declare @pubID varchar(40) = '0736,1389,9901'
GO
select *
from dbo.publishers
where pub_id in (@pubID)

----
(0 row(s) affected)

The problem is that I have a list of varchar of PubId as input,
and this field is integer.

I need something that translate ''0736,1389,9901' in

....
IN (0736,1389,9901)

How can I solve?

Thanks

Luigi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-24 : 07:42:04

As you found out, using it that way does not work. Instead, a quick solution would be to do the following:

declare @pubID varchar(40) = '0736,1389,9901'

select * 
from dbo.publishers
where ','+@pubID+',' like '%,'+pub_id+',%'

That would work, but might not be the best from a performance perspective (because the presence of the '%,' would make it impossible for SQL to use any indexes you may have on pub_id column.

Another possibility is to split the comma-separated string into a (virtual) table and join with that table. There are many string splitters that you can find if you google. One of my favorites is here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
If you install the function in Fig.21 of that article, then you can do this:

declare @pubID varchar(40) = '0736,1389,9901'
select p.* 
from dbo.publishers p
INNER JOIN dbo.DelimitedSplit8K(@pubID,',') s ON s.Item = t.pub_id

BTW, don't put a batch separator (GO statement) between your variable declaration and the rest of the code. The scope ends with the batch separator, so the variable will not be available in the select query if you do.

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-03-24 : 09:14:41

Thank you Sunita, I'll try with those function.

Luigi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources