using the IN statement

[vlitim]

I have a comma delimited list in a column and I want to see if a number appears in it ie.

select * from table where 68 IN (staffID)

now it works if there is only one number in the col but as soon as more appear nothing is returned

I am sure I have this working somewhere before but now it seems beyond me! Obviously I am doing something wrong

Cheers
Tim

 

[LFCfan]

Hi Tim,

check out FAQ183-3132

 

[vlitim]

I am doing it the other way around though so

@staffID = 3

select * from table where @staffID IN (staffID)

where staffID in the table = '3,35,65'

Tim

 

[swampBoogie]

select * from t where 
charindex(',68,',',' + staffID + ',') > 0

 

[LFCfan]

Doh!
sorry Tim, i've obviously got a touch of the fridays

 

[vlitim]

swampBoogie I have tried that but if there is nothing in the staffID col then it doesn't return the record which it should do.

 

[JamesLean]

So if the staffid column is NULL then you always want the row returned, but if there are values in staffid then you only want it returned if it contains the value you pass in?

SELECT * FROM t
WHERE CHARINDEX(',' + CAST(@staffid AS varchar) + ',', ',' + staffid + ',') > 0
  OR staffid IS NULL

--James

 

[maswien]

Try this:

select * from table where replace(staffID, ' ', '') like '%,' + @staffID + ',%' OR staffid IS NULL

This is more robust cause it handles '3, 35 , 65'

 

[vlitim]

Why can't you use the IN statement for this??

 

[iker3000]

Look for the t-sql refference of IN and LIKE in SQL-help or on the internet:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp

IKER