Last updated on June 11th, 2019 at 07:10 pm
This SQL tutorial provides explanations, examples for NVL function in Oracle
NVL function is a nesting function which is used to substitute null values with certain values
In the above syntax
expr1 : it is the source values or expression which may contain null. You can provide column name,function on the column name
expr2 : It is the target value which will be placed in case null is returned from expr1
If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
Important Points to note
1) The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error.
If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.
So following statement would succeed
select nvl(num-col ,10) from <table> select nvl(char-col ,'NA') from <table> select nvl(end_date,'01-MAY-18') from <table>
Following statement would do implicit conversion
select nvl(char-col ,1) from <table>
Following statement would fail
select nvl(end_date,'m/a') from <table> select nvl(user_id,'abc') from <table>
Usage
This function is very useful where we dont want to return null values or in numeric calculation as null would turn whole values into null
Lets take an example to understand it
We have sales_people table which contain the sales done’s by sales people,commission % on the sales ,salary of the people. We want to calculate total monthly incomes for the sales people. Sales can have null values
Statement would be
select name,(salary + sales*commission/100) Monthy_income from sales_people;
It will return null values of all the people who has not done any sales in that period as calculation will have null values
This can be fixed by using nvl function as
select name,(salary + nvl(sales,0)*commission/100) Monthy_income from sales_people;