[sql] CASE IN statement with multiple values

Is there a way to make a CASE statement with an IN clause?

SELECT
CASE c.Number
IN ('1121231','31242323') THEN 1
IN ('234523','2342423') THEN 2
END AS Test
FROM tblClient c

This question is related to sql sql-server sql-server-2005

The answer is


The question is specific to SQL Server, but I would like to extend Martin Smith's answer.

SQL:2003 standard allows to define multiple values for simple case expression:

SELECT CASE c.Number
          WHEN '1121231','31242323' THEN 1
          WHEN '234523','2342423' THEN 2
       END AS Test
FROM tblClient c;

It is optional feature: Comma-separated predicates in simple CASE expression“ (F263).

Syntax:

CASE <common operand>
     WHEN <expression>[, <expression> ...] THEN <result>
    [WHEN <expression>[, <expression> ...] THEN <result>
     ...]
    [ELSE <result>]
END

As for know I am not aware of any RDBMS that actually supports that syntax.


You can return the same value from several matches:

SELECT
  CASE c.Number
    WHEN '1121231' THEN 1
    WHEN '31242323' THEN 1
    WHEN '234523' THEN 2
    WHEN '2342423' THEN 2
  END AS Test
FROM tblClient c

This will probably result in the same execution plan as Martins suggestion, so it's more a matter of how you want to write it.


If you have more numbers or if you intend to add new test numbers for CASE then you can use a more flexible approach:

DECLARE @Numbers TABLE
(
    Number VARCHAR(50) PRIMARY KEY
    ,Class TINYINT NOT NULL
);
INSERT @Numbers
VALUES ('1121231',1);
INSERT @Numbers
VALUES ('31242323',1);
INSERT @Numbers
VALUES ('234523',2);
INSERT @Numbers
VALUES ('2342423',2);

SELECT c.*, n.Class
FROM   tblClient c  
LEFT OUTER JOIN   @Numbers n ON c.Number = n.Number;

Also, instead of table variable you can use a regular table.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?