1.NVL (EXP1,EXP2)
NVL
lets you replace null (returned as a blank) with a string in the results of a query. If expr1
is null and then NVL
returns expr2
. If expr1
is isn't null, then NVL
returnsexpr1
.
The function is to handle null values in an expression: Assuming that the expression EXP1 is null, the function returns the value of the expression Exp2,
Assuming that the expression EXP1 is not a null value, the function returns the value of the expression Exp1.
2.NVL2 (EXP1,EXP2,EXP3)
NVL2
lets determine the value returned by a query based on whether a specified expression was null or not NULL. Ifexpr1
is isn't null, thenNVL2
returnsexpr2
. Ifexpr1
is null and thenNVL2
returnsexpr3
.
This function is determined to return an expression when EXP1 is null and non-null values:
If EXP1 is a null value, the EXP3 is returned
assume that EXP1 is non-null and returns EXP2.
3.coalesce (EXP1,EXP2, ...)
COALESCE
returns the first non-null expr
In the expression list. You must specify at least and expressions. If all occurrences expr
of evaluate to NULL, then the function returns NULL.
The function returns the first non-null value in the full expression in parentheses, assuming that it is null, and returns a null value with a minimum of two expressions.
Example:
This article from "Angel Wings" blog, declined reprint!
The difference between several functions of Oracle NVL,NVL2,COALESCE