Return expr1 if not NaN, otherwise return expr2.
NANVL(expr1, expr2)## Overview Returns expr1 if it is not NaN, otherwise returns expr2. NANVL is the NaN counterpart of NVL for floating-point data. It does not treat NULL as NaN: if expr1 is NULL, NANVL returns NULL rather than expr2. Use NANVL to substitute a safe value for undefined floating-point computations (division by zero producing NaN, square root of negatives, log of zero). Combine with NVL or COALESCE when both NULL and NaN must be replaced. ## Behavior - Returns expr1 when expr1 is a finite number or infinity. - Returns expr2 when expr1 is IEEE 754 NaN. - Returns NULL when expr1 is NULL (does not replace NULL). - Result type is the common supertype of expr1 and expr2, typically DOUBLE. - Only floating-point inputs are meaningful; integer inputs cannot be NaN and pass through unchanged. ## Compatibility - NANVL is available in most SQL engines that expose IEEE 754 floats; semantics are consistent. - Not part of ANSI SQL; some dialects require CASE WHEN ISNAN(expr1) THEN expr2 ELSE expr1 END instead.
| Name | Type | Description |
|---|---|---|
expr1 | Specifies the floating-point expression tested for NaN. If not NaN, this value is returned. | |
expr2 | Specifies the fallback value when expr1 is NaN. Must be numeric. |
-- Non-NaN value returned unchanged
SELECT NANVL(3.14, 0.0) AS v; -- 3.14-- NaN triggers the fallback
SELECT NANVL(CAST('NaN' AS DOUBLE), 0.0) AS v; -- 0.0-- NULL is not NaN, so NULL is returned (not replaced)
SELECT NANVL(NULL, 0.0) AS v; -- NULL-- Replace undefined division results
SELECT NANVL(0.0 / 0.0, -1.0) AS v; -- -1.0-- Realistic: safe ratio with both guards
SELECT customer_id,
NANVL(lifetime_spend / NULLIF(lifetime_sessions, 0), 0.0) AS avg_spend_per_session
FROM analytics.customers.lifetime;