Oracle NVL Function :

In my previous articles i have explained about different SQL tutorials with real life examples. My main motive is to give you best idea about different concepts of oracle with real industry examples. In this article i will give you the Oracle NVL Function with real industry examples. This is most used SQL function in SQL as well as PL SQL for programming. There are lot of situations where user need to return some value for null values for those cases Oracle NVL function is widely used. I will explain multiple real life examples of Oracle NVL Function so that user will understand the concept easily and implement this concept in day to day life examples.

Oracle NVL Function Syntax and Examples :

In this section i will try to explain the syntax of Oracle NVL Function with its real examples.

The key use of NVL function is to replace the null value to some value. There are lot of situations where user needs to use this NVL function on frequent basis.

Purpose : 

Oracle NVL Function is used to replace the null value with some specific value.

Syntax :

NVL( Column_name,Replace_value);

The above syntax is used to replace the null value with single value where column_name is name of column in the table in which user wants to replace the string. The Replace_value is value which user wants to replace.

There are lot of situations where user needs to replace the null value in the column with some value.I would like to give you some most important examples where we need to use NVL function. The NVL function is not only useful in simple select statements of SQL but also the analytics as well as PL SQL blocks.

Tip : You can not use distinct clause within NVL function. If you want to use distinct clause use it before NVL.

Wrong :

Select NVL(distinct  Department,’NA’) from Employees;

Right :

Select distinct NVL(Department,’NA’) from Employees;

Example 1 :  The situation where user needs to use ‘NA’ (Not Applicable)

There are so many situations where business user needs to use the phrase ‘NA’ or Not Applicable. In that case user needs to use NVL function.

Select Employee_name,NVL(Department,’NA’) from Employees;

The above example will give you the Employee name and its associated Department. If department is not allocated to employee that department should be null. The above statement will replace the Null value with the phrase ‘NA’. Otherwise it will return Employee with department.

Example 2 : Replace Null value with other column value

In most of cases due to design change some values are stored in one column and some values are stored in other column. To deal with this situation NVL function is very useful. Let us take the example that some department values for employees are stored in to ‘department’ column but some are in to department_1 column. In this situation if user needs to display the employees with allocated departments we need to use NVL function as follows :

Select Employee_name,NVL(Department,Department_1) from Employees;

The above query will return the Employee_name and its associated department.If Department column has null value it will return the value from Department_1 column.

Example 3 : NVL Function used to indicate numbers.

There are situations where user needs to indicate the null value with value Zero. Lets take example that if the bunch of employees got no increment but in table the data is showing null. To handle these kind of situations Oracle NVL Function is widely used.

Select Employee_name, NVL (Increment,0) from Employees;

The above query is used to show the employees with no increment as value ‘0’.

Example 4 : NVL Function in Business Intelligence

The NVL function is helpful when you want to store the fact that there is no data for a particular column, but you want to display something else.

Lets take a simple example regarding this. We need to store the start date as well as End date in database;but most of the times the start date is there but end date is not there. Lets take example of Employee table only. There should be start date and end date of employee. The employees working with that organization in present does not have end date.So the End date for those employees are null.When we are showing this in the report we need to show something at the place of null value for better display of report.

Select Employee_name,Start_date,NVL(End_Date,’31-Dec-9999′) from Employees;

The above query will give you the Employee_name its start_date and the end date is 31-Dec-9999 where the value is null.

Example 5 : Populating the missing data temporarily

There are situations where the data is missing. If user want to see the data in the specified report Oracle NVL function is used.Lets take the example of status column. If some data is missing for status column and null values are there. To replace null values the oracle nvl function is used.

Select Employee_name,NVL(Status,’No Status’) from Employees;

Here the employees whose status is null will be showed as ‘No Staus’

These are some most important examples of NVL function. The NVL function is most commonly and widely used function of oracle. Hope you will get some help with this article to understand the Oracle NVL function.