Geek Answers Handbook

SQL Server functional equivalent for PostgreSQL "in"

In postgres, you can perform a comparison with several elements, for example:

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

Same as doing:

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

Is there a functional equivalent for SQL Server?

+3
source share
3 answers

It is supported, but you will need to put an expression somewhere that takes a logical expression. For example, in a case expression:

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

Or a where :

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

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

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

This will give 1 if the “test” is in the comparison set 1 or more times, or 0 if it is not.

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

Please note that the cast is not strictly necessary, but can be useful if it is called from another language ... Then it must be solved in a logical way.

EDIT: According to the MSSQL query planner, this runs 4 times faster than the CASE method. YMMV.

0
source

More articles:


All Articles