Snowflake DECODE Function Syntax and Examples

Post author:Vithal S
Post last modified:January 15, 2020
Post category:Snowflake
Reading time:4 mins read

Snowflake cloud data warehouse supports the DECODE function which is similar to other relational databases such as Redhift, Netezza, Oracle, SQL Server, etc. The DECODE function is used to implement simple case statements. i.e. you can use it to implement simple if-then-else statement. This command is similar to the Snowflake CASE statement.

Snowflake DECODE Function Syntax and Examples

Snowflake SQL DECODE Function

The DECODE function compares the select expression to each search expression in order. As soon as a search expression matches the selection expression, the corresponding result expression is returned.

Snowflake SQL DECODE Function Syntax

Following is the syntax of the DECODE function. The syntax is pretty much similar to DECODE statement available in any other database.

DECODE( <expr> , <search1> , <result1> [ , <search2> , <result2> ... ] [ , <default> ] )

Where,

expr: is the select expression that you want to compare, such as a column in a table or an expression.
search1: is the target value that is compared against the select expression.
result1: is the replacement value that query returns when the expression matches the search1 value.
default: is an optional value. If none of the search expressions match the select expression, then returns DECODE this default value.

You can provide any number of target search expression in your DECODE statement.

Snowflake DECODE Function Examples

There are various usage of the DECODE statement in databases. For example, convert abbreviation to actual values. Another example would be to map country code with country names.

Following are some of basic examples of using DECODE function in Snowflake.

SELECT cnt, Decode(cnt, 1, 'One', 
                   2, 'Two', 
                   3, 'Three', 
                   'None') AS counters 
FROM   (SELECT 1 AS cnt 
        UNION ALL 
        SELECT 2 AS cnt 
        UNION ALL 
        SELECT 3 
        UNION ALL 
        SELECT 4 AS cnt) a;

+-----+----------+
| CNT | COUNTERS |
|-----+----------|
|   1 | One      |
|   2 | Two      |
|   3 | Three    |
|   4 | None     |
+-----+----------+

Snowflake DECODE with NULL Values

If the select expression value and the search value are NULL, then the DECODE statement returns the corresponding result value. A NULL value in the select expression matches a NULL value in the search expressions.

For example, consider following example to test NULL values in the DECODE command in Snowflake.

select DECODE(NULL, NULL,'matching','not matching') as DECODE_WITH_NULL;
+------------------+
| DECODE_WITH_NULL |
|------------------|
| matching         |
+------------------+