Home » Oracle » Oracle Sql » How to use NVL() function in Oracle

How to use NVL() function in Oracle

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

NVL function in Oracle

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

See also  duplicate target database for standby from active database

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
Rownum in Oracle
Auto Increment in Oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top