IS NAN Predicate

An IS NAN predicate determines whether an expression is the undefined result of an operation (that is, is "not a number" or NaN).

SQL syntax

Expression IS [NOT] NAN

Parameters

Parameter Description

Expression

Expression to test.

Description

  • An IS NAN predicate evaluates to TRUE if the expression is "not a number."

  • An IS NOT NAN predicate evaluates to TRUE if expression is not "not a number."

  • The expression must either resolve to a numeric data type or to a data type that can be implicitly converted to a numeric data type.

  • Two NaN ("not a number") values are equal to each other.

  • Expressions containing floating-point values may generate Inf, -Inf, or NaN. This can occur either because the expression generated overflow or exceptional conditions or because one or more of the values in the expression was Inf, -Inf, or NaN. Inf and NaN are generated in overflow or division by 0 conditions.

  • Inf, -Inf, and NaN values are not ignored in aggregate functions. NULL values are. If you want to exclude Inf and NaN from aggregates (or from any selection), use both the IS NOT NAN and IS NOT INFINITE predicates.

  • NaN ("not a number") sorts higher than all other values including positive infinity, but lower than the NULL value.

  • See "INF and NAN" for more information on Inf and NaN.