The NVl, NVl2, COALESCE functions are used to check a column, a variable, or an expression for NULL value. In oracle database NULL value is identified as “No value” or “Nothing” or “undefined”. Some records in the tables permit NULL values. In earlier versions of oracle database 7.3, NVL function is mainly used to check whether any column in a particular record is NULL or not.
NVL FUNCTION accepts two arguments.
Syntax :
NVL( arg1, arg2)
if argument1 is NULL , the NVL function returns argument2 else NVL function returns argument1.
if arg1 is NULL THEN
RETURN ARG2
ELSE
RETURN ARG1
END IF
Ex:
I want to display all the emp records and their salary should be displayed as zero if the salary field is null or not defined.
select emp_id, emp_name, NVL(SALARY,0) from emp;
I want to display all the emp records and their salary should be displayed as “Not Applicable” if the salary field is null.
select emp_id, emp_name, NVL(TO_CHAR(SALARY),'Not Applicable') from emp;
So why did i use the to_char function to achieve the result ? Because Oracle always tries to convert the argument 2 datatype to the argument 1’s datatype. if Argument-1’s data type is character , then the argument-2 data type can be any data type, but should be in a position to convert to Argument-1 datatype.
NVL2 FUNCTION : NVL2 function accepts three parameters.
NVL2(ARG1, ARG2, ARG3)
if arg1 is null then
return ARG2
ELSE
retunr ARG3
END IF.
I want to display all the emp records and their salary field should display “Applicable” if the salary is present otherwise it should display “Not Applicable”.
ex:
select emp_id,emp_name,NVL2(SALARY, 'Applicable', 'Not Applicable') from emp
In NVL2 function argument-3 datatype should be in a way castable to argument-2 datatype.
COALESCE Function :
accepts an arguments list.
syntax
COALESCE (arg1,arg2,…..argn)
COALESCE function returns first non null value in the argument list tracing or scanning from left to right. if all the arguments are null values then return null.
if arg1 is not null then
return arg1
elseif arg2 is not null then
return arg2
………
elseif arg-n is not null then
return arg-n
else
return null
end if
Coalesce example :