decode (key) function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 9.1 and above

Returns the value matching the key.

Syntax 

decode(expr, { key1, value1 } [, ...] [, defValue])

Arguments

  • expr: Any expression of a comparable type.

  • keyN: An expression that matched the type of expr.

  • valueN: An expression that shares a least common type with defValue and the other valueNs.

  • defValue: An optional expression that shares a least common type with valueN.

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 

> SELECT decode(5, 6, 'Spark', 5, 'SQL', 4, 'rocks');
 SQL

> SELECT decode(NULL, 6, 'Spark', NULL, 'SQL', 4, 'rocks');
 SQL

> SELECT decode(7, 6, 'Spark', 5, 'SQL', 'rocks');
 rocks