NANVL

Syntax

NANVL
(
  value         IN { real, double precision, numeric },
  replacement   IN { varchar, real, double precision, numeric }
)
RETURNS { real, double precision, numeric };

Overview

The NANVL function returns the value of the second argument if the first argument is NaN (Not a Number), otherwise it returns the value of the first argument as it is.

If the second argument is not varchar, it must be of the same type as the first argument. Used to replace NaN with another value in floating-point or numeric data.

Parameters

Parameter	Description
value	The numeric value to check (real, double precision, numeric). if NaN, returns the replacement value.
replacement	The value to return instead when value is NaN (varchar, real , double precision , numeric ). if it is a varchar, it is automatically cast to the type of value.

Example

# test 1
SELECT oracle.NANVL(3.14, 0.0); -- result: 3.14 (return the original value as 3.14 is not NaN)

 nanvl 
-------
  3.14
(1 row)

# test 2
SELECT oracle.NANVL('NaN'::float4, 0.0); -- result: 0.0 (return the 0.0,replacement value, as 1st value is NaN)

 nanvl 
-------
     0
(1 row)