Oracle NVL()

Introduction to Oracle NVL()

The Oracle NVL () function in Oracle can be defined as a function which allows the user to replace the null value or an empty string in the result set with a better alternative like a string value and this function returns the not non value of the same data type of the base expression in case the value is not null and it replaces the null or empty string with a different alternative (passed as an argument with the function) in case the original value is null.

Syntax:.

NVL(expression, replacement expression)

Parameters:

• expression: It refers to the expression which we want to replace in case it is null.
• replacement expression: It refers to the value with which we will replace the null value.

How NVL() Function work in Oracle?

Here we can see in the syntax of the function that it takes two arguments. The first one is a field name or expression and the second one is a value which will replace the null value. So when a statement having this query is executed in Oracle. The function immediately checks the values of the field or expression in all the rows returned by the select statement.

If the value of the field or column is not null then it will return the original value but in case it finds any one of the row in the column having a null value then it replaces that particular value with whatever value we have given in the second parameter of the function. It then returns the modified result set as output.

Examples of Oracle NVL()

Given below are the examples of Oracle NVL():

Example #1

Replace null value with zero using NVL function.

In this scenario we will try to replace the age of the employees which are null with zero so that the null values in age column of the employee table are replaced with the number zero. The NVL function will only be applied on the age column.

Let us look at the table records before the NVL function is applied.

Code:

select * from employee;

Output:

As we can see in the age column there is a null value . So let us now write a query to replace the null value with number 0.

Code:

select employee_id, name, NVL(age, '0') from employee;

Output:

Now we will run the query in SQL developer and look the result.

As we can see in the above screen shot that the null value in age column has been replaced with number zero.

Example #2

Replace a null value with a string.

In this scenario we are going to replace a null value of a field with a string. In the example for this scenario we are going to try to replace the null value present in the city column of the employee table with a string ‘NOT AVAILABLE’ using NVL function.

Let us look at the employee table data before we apply NVL function.

Code:

select * from employee;

Output:

As we can see in the employee table data the city column has a null value.

Let us now replace the null value in the city column with a ‘NOT AVAILABLE’ string.

Code:

select employee_id, name, NVL(city, 'NOT AVAILABLE') from employee;

Output:

Let us now execute the query and check the result in SQL developer.

As we can see in the output above the null value in the city column has been replaced by the string ‘NOT AVAILABLE’.

Example #3

Replace a null value with a value from another field.

In this scenario we are going to replace the null value of one field with value from another field or column.

In this example we will replace the null value stored in the city column of the employees table with the value of vehicle_name column of the same row.

Let us look at the table data before applying the NVL function.

Code:

select * from employee;

Output:

As we can see in the above screen shot that the null value in the city column will be replaced by the value ‘Tata’ present under the field vehicle_name in the same row.

Let us now write a query for the same using NVL function.

Code:

select employee_id, name, vehicle_name, NVL(city, vehicle_name) from employee;

Output:

Let us now run the query in SQL developer and look at the result.

As we can see in the above screen shot that the null value has been replaced by the value ‘Tata’ which is a value in the column vehicle_name present in the same row.

Example #4

Using distinct function with NVL function.

So we all are aware that DISTINCT function is used to return the unique values present in a field. In this example we are going to find the distinct cities present in the city column of the table employee. We are going to use NVL because we have a null value present in the city column and we are going to replace that using NVL function as distinct function will return null also as a unique value.

Let us look at the data before applying NVL function.

Code:

select * from employee;

Output:

Let us now write a query to find the distinct values and replace the null value with the city ‘Bhopal’.

Code:

select DISTINCT( NVL(city, 'Bhopal')) from employee;

Output:

Let us run the query in SQL developer and check the result.

As we can see in the output the null value has been replaced by ‘Bhopal’ and the query returns the distinct cities in city column.

Conclusion

In this article we have seen about the definition of NVL function and its syntax. We also saw working of the function in Oracle and later on in the article we went through various scenarios along with examples.

Recommended Articles

This is a guide to Oracle NVL(). Here we discuss the introduction, how NVL() function work in oracle? and examples respectively. You may also have a look at the following articles to learn more –

1. Oracle While Loop
2. Oracle Window Functions
3. Oracle LENGTH()
4. Oracle ROLLBACK