Oracle NVL in Snowflake, Databricks and BigQuery
Snowflake and Databricks support NVL directly. BigQuery uses IFNULL. The bigger migration risk is Oracle treating empty strings as NULL, which none of the targets do.
Oracle function: NVL(expr1, expr2) · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | NVL(expr1, expr2) | Alias of IFNULL. | Snowflake docs |
| Databricks | Same name | nvl(expr1, expr2) | Synonym of coalesce(expr1, expr2). | Databricks docs |
| BigQuery | Different name | IFNULL(expr, null_result) | COALESCE also works. | BigQuery docs |
Behavior differences to check
- Oracle stores '' as NULL, so NVL(col, 'n/a') also replaces empty strings there. In the targets an empty string is not NULL; use NVL(NULLIF(col, ''), 'n/a') to keep the Oracle result.
- Oracle NVL converts expr2 to the type of expr1. The targets use a common type of both arguments, which can change numeric precision or string length in the result.
Examples
Show a placeholder for missing regions
SELECT customer_id, NVL(region, 'unknown') AS region FROM customers;SELECT customer_id, NVL(NULLIF(region, ''), 'unknown') AS region FROM customers;SELECT customer_id, nvl(nullif(region, ''), 'unknown') AS region FROM customers;SELECT customer_id, IFNULL(NULLIF(region, ''), 'unknown') AS region FROM customers;