All Oracle NULL related Functions

NULLIF function

The NULLIF function returns a NULL value if both parameters are equal in value. If the parameters are not equal, it returns the value of the first parameter. The following query would return NULL.

SELECT NULLIF(1,1) FROM dual;

COALESCE function

The COALESCE function returns the first non-NULL value in an expression list. If all expressions are null it returns NULL. The following query would return '5'.

SELECT COALESCE(NULL, NULL, '5') FROM dual;

NVL function

The Oracle/PLSQL NVL function Converts NULL value to actual value.The following query will return 5.

Syntax:
       NVL( value, replace_if_value_is_null )

SELECT NVL(NULL,5) FROM dual;

NVL2 function

The Oracle/PLSQL NVL2 function extends the functionality found in the NVL functions. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.

If expr1 is not NULL, NVL2 returns expr2. If expr1 is NULL, NVL2 returns expr3. The argument expr1 can have any data type.

Syntax:
       NVL2( expr1,  value_if_expr1_not_null, value_if_expr1_null )

SELECT NVL2(NULL,5,7) FROM dual;

Output : 7 

SELECT NVL2('dEexams',5,7) FROM dual;

Output : 5

LNNVL function

The Oracle/PLSQL LNNVL function is used in the WHERE clause of a SQL statement to evaluate a condition when one of the operands may contain a NULL value.

Syntax:
       LNNVL( condition )

Returns of LNNVL

Example:

Consider the product table with below data.

Now if we wanted to find all the product whose qty was below their sale.The SQL will be as follows .

SELECT * FROM product WHERE qty < sale;

Output :
         prod_id     qty      sale
         2           200      300

However, if we wanted to see the products that were below their sale as well as NULL sales, we would use the LNNVL functions as follows:

SELECT * FROM product WHERE LNNVL(qty >= sale) ;

Output :
         prod_id     qty      sale
         1           100      NULL
         2           200      300

NANVL function

The Oracle/PLSQL NANVL function lets you substitute a value for a floating point number such as BINARY_FLOAT or BINARY_DOUBLE, when a Nan (Not a number) value is encountered. This is most commonly used to convert Nan (Not a number) values into either NULL or 0.

Syntax:
       NANVL( value, replace_with )

SELECT NANVL(binary1,0) FROM test;

Output : The SQL statement above would return 0 if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value. 

SELECT NANVL(binary1,NULL) FROM test;

Output : The SQL statement above would return NULL if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.