This Oracle SQL tutorial provides explanations, examples for NVL function in Oracle
What is NVL() function in Oracle
NVL function in Oracle is a nesting function that 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 that 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 data type. If their data types 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>
The following statement would do implicit conversion
select nvl(char-col ,1) from <table>
The following statement would fail
select nvl(end_date,'m/a') from <table> select nvl(user_id,'abc') from <table>
How to use NVL() function
This function is very useful where we don’t want to return null values or in the numeric calculation as null would turn whole values into null
Let’s take an example to understand it
We have a sales_people table which contains the sales done’s by salespeople, commission % on the sales, the salary of the people. We want to calculate the total monthly incomes for the salespeople. 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 have 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) Monthly_income from sales_people;
So it is clearly very helpful while doing Mathematical operations. NVL Function in Oracle is quite a useful function and it can be used in many places
Also Reads
oracle sql date functions
NULLIF function in Oracle
Update statement in oracle
Coalesce Function in Oracle
change date format in oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm
Leave a Reply