Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
Databricks SQL Databricks Runtime
Returns the value matching the key. decode compares expr to each keyN in order and returns the corresponding valueN for the first match (like a key-value lookup or switch). If no key matches, it returns defValue when provided, otherwise NULL.
Syntax
decode(expr, { key1, value1 } [, ...] [, defValue])
Arguments
expr: Any expression of a comparable type.keyN: An expression that matched the type ofexpr.valueN: An expression that shares a least common type withdefValueand the othervalueNs.defValue: An optional expression that shares a least common type withvalueN.
Returns
The result is of the least common type of the valueN and defValue.
The function returns the first valueN for which keyN matches expr.
For this function NULL matches NULL.
If no keyN matches expr, defValue is returned if it exists.
If no defValue was specified the result is NULL.
Examples
-- Compare expr (5) to keys in order: 6? No. 5? Yes. Return the value for 5, which is 'SQL'.
> SELECT decode(5, 6, 'Spark', 5, 'SQL', 4, 'rocks');
SQL
-- NULL matches NULL in decode. expr is NULL; the second key is NULL, so that pair matches. Return 'SQL'.
> SELECT decode(NULL, 6, 'Spark', NULL, 'SQL', 4, 'rocks');
SQL
-- No key matches 7 (not 6, not 5). No fourth key-value pair, so defValue 'rocks' is returned.
> SELECT decode(7, 6, 'Spark', 5, 'SQL', 'rocks');
rocks