Oracle NVL2 in Snowflake, Databricks and BigQuery
Snowflake and Databricks support NVL2 directly. BigQuery has no NVL2; write IF(expr IS NOT NULL, a, b).
Oracle function: NVL2(expr1, expr2, expr3) · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | NVL2(expr1, expr2, expr3) | Returns expr2 if expr1 is not NULL, otherwise expr3. | Snowflake docs |
| Databricks | Same name | nvl2(expr1, expr2, expr3) | Synonym of CASE WHEN expr1 IS NOT NULL THEN expr2 ELSE expr3 END. | Databricks docs |
| BigQuery | Rewrite | IF(expr1 IS NOT NULL, expr2, expr3) | No NVL2 function. | BigQuery docs |
Behavior differences to check
- Oracle treats '' as NULL, so NVL2(col, 'set', 'empty') returns 'empty' for empty strings there. Add NULLIF(col, '') in the targets to keep that result.
Examples
Flag customers with an email
SELECT id, NVL2(email, 'yes', 'no') AS has_email FROM customers;SELECT id, NVL2(NULLIF(email, ''), 'yes', 'no') AS has_email FROM customers;SELECT id, nvl2(nullif(email, ''), 'yes', 'no') AS has_email FROM customers;SELECT id, IF(NULLIF(email, '') IS NOT NULL, 'yes', 'no') AS has_email FROM customers;