Oracle DECODE in Snowflake, Databricks and BigQuery
Snowflake and Databricks support Oracle-style DECODE, including NULL matching NULL. BigQuery has no DECODE; rewrite it as a CASE expression and handle NULL explicitly.
Oracle function: DECODE(expr, search, result [, search, result]... [, default]) · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | DECODE(expr, search1, result1 [, ...] [, default]) | A NULL expr matches a NULL search value, as in Oracle. | Snowflake docs |
| Databricks | Same name | decode(expr, key1, value1 [, ...] [, defValue]) | NULL matches NULL. Not to be confused with decode(bin, charset), which decodes binary data. | Databricks docs |
| BigQuery | Rewrite | CASE expr WHEN search1 THEN result1 ... ELSE default END | A simple CASE never matches NULL; add WHEN expr IS NULL THEN ... first. | BigQuery docs |
Behavior differences to check
BigQuery
- DECODE(status, NULL, 'none', ...) becomes CASE WHEN status IS NULL THEN 'none' ... END. Translating it to CASE status WHEN NULL silently never matches.
Examples
Status code to label
SELECT order_id, DECODE(status, 'P', 'pending', 'S', 'shipped', NULL, 'none', 'other') AS status_label FROM orders;SELECT order_id, DECODE(status, 'P', 'pending', 'S', 'shipped', NULL, 'none', 'other') AS status_label FROM orders;SELECT order_id, decode(status, 'P', 'pending', 'S', 'shipped', NULL, 'none', 'other') AS status_label FROM orders;SELECT order_id, CASE WHEN status IS NULL THEN 'none' WHEN status = 'P' THEN 'pending' WHEN status = 'S' THEN 'shipped' ELSE 'other' END AS status_label FROM orders;