General Functions

Functions	Description
NVL	Convert a null to an actual value
NVL2	If expression1 is not null, NVL2 return expression2. if expression1 is null, NVL2 return expression3
NULLIF	Compares two expressions and return null if they are equal, return the first expression if they are not equal.
COALESCE	Return the first non-null expression in the expression list

NVL

Select last_name, salary, NVL(commission_pct, 0)
From employees;

If the commission is null then it will convert to 0.


NVL2
Select last_name, salary, commission_pct, nvl2(commission_pct, 'SAL+COMM', 'SAL') income
From employees
Where department_id IN (50, 80);

If commission_pct is not null, the second expression of SAL+COMM is returned. If null then SAL will be displayed.

NULLIF
Select first_name, length(first_name) expr1, last_name, length(last_name) expr2, nullif (length(first_name), length(last_name)) result
From employees;

If values are equal then its null, if they are not, the function return to the first expression (EXPR1).

COALESCE
Select last_name, COALESCE(manager_id, commission_pct, -1) comm
From employees
Order by commission_pct;

If the manager_id value is not null, it’s displayed. If manager_id value is null, then the commission_pct is displayed. If the manager_id and commission_pct value are null, then value -1 is displayed.