Total Pageviews

October 5, 2016

10/05/2016 08:06:00 AM
Oracle SQL:What is the difference between NVL,NVL2 and COALESCE Functions?


Questions:

NVL : Converts null value to an actual value.
NVL2 : If first expression is not null, return second expression.
If first expression is null, return third expression. the first expression can have any data type.

NVL ( expr1 , expr2 ): If expr1 is null, then NVL returns expr2.
If expr1 is not null, then NVL returns expr1.
NVL2 ( expr1 , expr2 , expr3 ): If expr1 is null, then NVL2 returns expr3.
If expr1 is not null, then NVL2 returns expr2


COALESCE : Return first not null expression in the expression list.
The COALESCE function can be used in Oracle/PLSQL.
You could use the coalesce function in a SQL statement as follows:
SELECT COALESCE( address1, address2, address3 ) result
FROM suppliers;
The above COALESCE function is equivalent to the following IF-THEN-ELSE statement:
IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;