Snowflake Solutions Expertise and
Community Trusted By
Works like a cascading “if-then-else” statement. In the more general form, a series of conditions are evaluated in sequence. When a condition evaluates to TRUE, the evaluation stops and the associated result (after THEN) is returned. If none of the conditions evaluate to TRUE, then the result after the optional ELSE is returned, if present; otherwise NULL is returned.
CASE WHEN <condition1> THEN <result1> [ WHEN <condition2> THEN <result2> ] [ ... ] [ ELSE <result3> ] END CASE <expr> WHEN <value1> THEN <result1> [ WHEN <value2> THEN <result2> ] [ ... ] [ ELSE <result3> ] END
CASE:
This shows that if none of the values match, and there is noOutput:SELECT column1, CASE WHEN column1=1 THEN 'one' WHEN column1=2 THEN 'two' ELSE 'other' END AS result FROM (values(1),(2),(3)) v;+---------+--------+ | COLUMN1 | RESULT | |---------+--------| | 1 | one | | 2 | two | | 3 | other | +---------+--------+
ELSE clause, then the value returned is NULL:
This example handles NULL explicitly.Output:SELECT column1, CASE WHEN column1=1 THEN 'one' WHEN column1=2 THEN 'two' END AS result FROM (values(1),(2),(3)) v;+---------+--------+ | COLUMN1 | RESULT | |---------+--------| | 1 | one | | 2 | two | | 3 | NULL | +---------+--------+
The following example combinesOutput:SELECT column1, CASE WHEN column1 = 1 THEN 'one' WHEN column1 = 2 THEN 'two' WHEN column1 IS NULL THEN 'NULL' ELSE 'other' END AS result FROM VALUES (1), (2), (NULL);+---------+--------+ | COLUMN1 | RESULT | |---------+--------| | 1 | one | | 2 | two | | NULL | NULL | +---------+--------+
CASE with collation:
SELECT CASE COLLATE('m', 'upper') WHEN 'M' THEN TRUE ELSE FALSE END; +----------------------------+ | CASE COLLATE('M', 'UPPER') | | WHEN 'M' THEN TRUE | | ELSE FALSE | | END | |----------------------------| | True | +----------------------------+ SELECT CASE 'm' WHEN COLLATE('M', 'lower') THEN TRUE ELSE FALSE END; +------------------------------------------+ | CASE 'M' | | WHEN COLLATE('M', 'LOWER') THEN TRUE | | ELSE FALSE | | END | |------------------------------------------| | True | +------------------------------------------+
WHEN <null_expr> = NULL THEN 'Return me!' does not return “Return me!”. If you want to compare to NULL values, use IS NULL rather than = NULL.condition#, expr, value, and result can all be general expressions and thus can include subqueries that include set operators, such as UNION, INTERSECT, EXCEPT, and MINUS. When using set operators, make sure that data types are compatible. For details, see the in the topic.condition#
In the first form of CASE, each condition is an expression that should evaluate to a BOOLEAN value (True, False, or NULL).
expr
A general expression.
value
In the second form of CASE, each value is a potential match for expr. The value can be a literal or an expression. The value must be the same data type as the expr, or must be a data type that can be cast to the data type of the expr.
result#
In the first form of the CASE clause, if condition# is true, then the function returns the corresponding result#. If more than one condition is true, then the result associated with the first true condition is returned.
In the second form of the CASE statement, if value# matches the expr, then the corresponding result is returned. If more than one value matches the expr, then the first matching value’s result is returned.
The result should be an expression that evaluates to a single value.
In both forms of CASE, if the optional ELSE clause is present, and if no matches are found, then the function returns the result in the ELSE clause. If no ELSE clause is present, and no matches are found, then the result is NULL. 
Copyright 2023 IT Strategists, Inc. All Rights Reserved. Community site to educate and connect Snowflake users, not part of Snowflake Inc.
