ISNAN

Return TRUE if the floating-point value is NaN, otherwise FALSE.

Category: conditionalReturns: BOOLEANDialect: Standard

Syntax

ISNAN(expr)

Description

## Overview Returns TRUE if the floating-point argument is IEEE 754 NaN (Not a Number), FALSE otherwise. NaN arises from undefined operations such as 0.0/0.0 or sqrt of a negative number. Use ISNAN to detect computation failures before they propagate. Pair with NANVL to substitute a safe value when NaN is encountered. ## Behavior - Returns TRUE only for IEEE 754 NaN. - Returns FALSE for finite numbers, positive infinity, and negative infinity. - Returns NULL for NULL input (follows three-valued logic). - Does not test INTEGER columns; integer types cannot hold NaN. ## Compatibility - NaN semantics follow IEEE 754, as applied by the engine's DOUBLE and FLOAT types. - SQL standard does not define ISNAN; behavior is consistent across most engines that expose IEEE 754 floats.

Parameters

Name	Type	Description
`expr`		Specifies the floating-point expression to test. Must be DOUBLE or FLOAT.

Examples

-- Normal number
SELECT ISNAN(1.0) AS v;  -- false

-- NaN literal
SELECT ISNAN(CAST('NaN' AS DOUBLE)) AS v;  -- true

-- NULL input returns NULL
SELECT ISNAN(NULL) AS v;  -- NULL

-- Filter out NaN rows
SELECT val
FROM (VALUES (1.0), (CAST('NaN' AS DOUBLE)), (3.0)) AS t(val)
WHERE NOT ISNAN(val);

-- Realistic: flag computation failures in a metrics table
SELECT metric_id, ts
FROM analytics.metrics.windowed
WHERE ISNAN(rolling_avg);

Pitfalls

NaN never compares equal to anything, including itself. Do not use column = 'NaN' or column = column; use ISNAN instead.
ISNAN(NULL) returns NULL, not FALSE. Combine with ISNULL when you want to treat both NULL and NaN as missing.
Only DOUBLE and FLOAT can be NaN. Calling ISNAN on an INTEGER raises a type error or implicit cast depending on the engine.
Positive and negative infinity are not NaN; ISNAN(CAST('Infinity' AS DOUBLE)) returns FALSE.
NaN is preserved through most arithmetic, so a single NaN in an aggregation can poison the result. Filter or substitute early.