SQL Server functional equivalent to PostgreSQL "in"

Question

In postgres you can do a comparison against multiple items like so:

 SELECT 'test' IN ('not','in','here');

Which is the same as doing:

  SELECT ('test' = 'not' OR 'test' = 'in' OR 'test' = 'here');

Is there a functional equivalent for SQL Server ?

Answer 1

this should give similar results in all recent versions of MSSQL. You could also write a scalar function to shorten things up a bit.

select case 
     when 'test' IN ('not', 'in', 'here') then 1
      else 0
      end;

Answer 2

It is supported, but you will need to put the expression somewhere that accepts a boolean expression. For example, in a case statement:

select  case  when 'test' in ('not','in','here')  then 1  else 0  end

----------- 
0

(1 row(s) affected)

Or a where clause:

select * from T where C in (1,3,5,7,9)

Answer 3

This will give 1 if 'test' is in the comparison set 1 or more times, or 0 if it isn't.

SELECT CAST(COUNT(*) AS BIT) as IsItHere WHERE 'test' IN('not','in','here')

Note that the cast isn't strictly necessary, but may be useful if this is called from another language... It should then resolve to a boolean.

EDIT: According to MSSQL Query planner, this is executed ~4 times faster then the CASE method. YMMV.

Answer 4

I should have just tried it in the case statement, I did the test select first and when it failed I assumed there wasn't support for it! Thanks guys :)