Toad World Blog

An Overview of the NVL and NVL2 function with examples

Mar 15, 2022 9:33:17 AM by Nisarg Upadhyay

Preparing data for analysis often means encountering some shortcomings within the data itself. Extra spaces, null values, blank values, wrong data types and duplicate values all stand in the way of properly evaluating data – that’s where the NVL and NVL2 functions can help make report output more meaningful.

Blog-TW-NVLfunction-IM-JY-71320

For example, you are asked to provide a report of employees of the company. For some employees, the secondary contact number is NULL. Instead of showing NULL, we must display some text like ‘Contact Not Available.’ You can achieve it using CASE, NVL, NVL2, COLEASE, DECODE functions. Among all these functions, we are going to learn about the NVL() and NVL2() functions.

Toad by Quest-1-1

Environment setup

I have installed Oracle 21c express edition on my workstation and created a pluggable database named EltechDB. I have created a table named ProductCatalogue in the database. The following query creates the ProductCatalogue table.

CREATE TABLE PRODUCTCATALOGUE
  
(
     
PRODUCT_ID          NUMBER,
     
PRODUCT_NAME        VARCHAR2(255 CHAR),
     
UNIT_PRICE          NUMBER(10, 2),
     
PRODUCT_DESCRIPTION VARCHAR2(500 CHAR),
     
TAX                 NUMBER
  
) 

Run the following query to insert dummy data in it.

Insert into ProductCatalogue (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,Product_description,Tax) values (1,'Boy''s Shirt (White)',29.55,'Shirt',3);

Insert into ProductCatalogue (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,Product_description,Tax) values (2,'Women''s Shirt (Green)',16.67,'Shirt',NULL);

Insert into ProductCatalogue (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,Product_description,Tax) values (3,'Boy''s Sweater (Green)',44.17,'Sweater',9);

Insert into ProductCatalogue (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,Product_description,Tax) values (4,'Boy''s Trousers (White)',43.71,NULL,5);

Insert into ProductCatalogue (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,Product_description,Tax) values (5,'Girl''s Shorts (Red)',38.28,'Shorts',NULL);

Insert into ProductCatalogue (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,Product_description,Tax) values (6,'Boy''s Socks (Grey)',19.16,NULL,NULL);

Insert into ProductCatalogue (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,Product_description,Tax) values (7,'Boy''s Socks (Black)',19.58,NULL,10);

Insert into ProductCatalogue (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,Product_description,Tax) values (8,'Boy''s Coat (Brown)',21.16,NULL,5);

Insert into ProductCatalogue (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,Product_description,Tax) values (9,'Women''s Jeans (Brown)',29.49,'Jeans',NULL);

Insert into ProductCatalogue (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,Product_description,Tax) values (10,'Women''s Skirt (Red)',30.69,'Skirt',NULL);

Now let us take a look to understand how the NVL() function works.

Understanding the NVL() function

The NVL function replaces the null value with some meaningful value. The syntax is the following:

SELECT NVL(expression_1, expression_2)

If expression_1 evaluates as NULL, it returns the value specified in expression_2. If expression_1 evaluates as NOT NULL, the function returns the value specified in expression_1.

Note that if the datatype of expression_1 and expression_2 are different, then Oracle performs an implicit conversion. If Oracle cannot perform the implicit conversion, it returns an error.

NVL () Example 1

Expression_1 evaluates as NULL in the following code, so the NVL() returned N/A (expression_2). Let us see another example.

SQL> select NVL(NULL, 'N/A') from dual;

NVL

---

N/A

Expresion_1 evaluates as NOT NULL in the following code, so the NVL() returned ‘Nisarg’ (expression_2).

SQL> select NVL('Nisarg','N/A') "Employee Name" from dual;

Employ

------

Nisarg

 

Example 2: Example with ProductCatalogue table

We are using the ProductCatalogue table of the EltechDB database. First, let us run the query to populate the list of the Products. Run the following query

select product_id,product_name,unit_price,product_description from productcatalogue

Query output showing product description with null values

As you can see in the query output, the table has four records with a NULL value in the Product_description column. Instead of showing NULL, we want to show the text ‘Product description is not available.’ To do that, the SQL query should be written as follows

select product_id,product_name,unit_price,NVL(product_description,'Product description is not available')"Product Description" from productcatalogue;

Query output showing null values replaced by using the NVL function

As you can see, the NULL values have been replaced with ‘Product description is not available.’ text. Let us take another example. There are a few records with NULL in the Tax column. Instead of displaying NULL, I want to display zero. To do that, run the following query:

select product_id,product_name,unit_price,NVL(product_description,'Product description is not available')"Product Description", NVL(tax,0)"Tax on Product" from productcatalogue;

 Query output showing replacement of null values with zeroes using NVL function

As you can see in the above screenshot, the NULL values in the Tax column have been replaced with zero.

Now, let us understand how the NVL2() function works

Understanding the NVL2() Function

The NVL2() function is an extended version of the NVL() function of the Oracle database. The syntax of the NVL2() function is as follows:

SELECT NVL(expression_1, expression_2, expression_3)

The NVL2 function evaluates expression_1. If expression_1 evaluates as NULL then it returns expression_3 and if expression_1 evaluates non-null values, the function returns expression_2.

NVL2() Example 1

Expression_1 evaluates as NULL in the following code, so the NVL2() returns Employee Does not exist (expression_3).

SQL> select NVL2(NULL,'S02263','Employee Does not exist') "Employee Name and Code" from dual;

Employee Name and Code

------------------------

Employee Does not exist

The following code, the expression_1 evaluates as NOT NULL, so the NVL2() returned ‘S02263’ (expression_2).

SQL> select NVL('Nisarg','N/A') "Employee Name" from dual;

Employ

------

Nisarg

Example 2: Example with ProductCatalogue table

We want to create a report using a query, and the logic is as follows:

  1. If the value in the tax column is NULL, then zero must be displayed. Also, the query should return the value of unit_price. See the following image for reference.

Unit price and final price of a product with zero tax displayed

  1. If the value in the tax column is NOT NULL value, then the query must calculate the percentage of the value in the tax column and display the final output by doing a sum of the unit_price and the calculated tax. See the following image for reference.

Final product price shown as sum of unit price and tax

To generate a report based on the above logic, the final query should be written as follows:

select product_id, product_name,unit_price, NVL(tax,0)"Tax on Product", NVL(NVL2(NVL(tax,0),((tax/100)*unit_price)+unit_price,unit_price),unit_price)"Final Price"

from productcatalogue

Below is the output.

Query output shown from NVL2 statement

As you can see, for the product_id =9, the query has returned the final price without calculating the tax. For the product_id=7, the query has returned the final price by calculating the tax and unit_price.

Get Toad for Oracle Base Subscription today

Subscription / eStore: buy up to 10 licenses at a time, get auto update, support, announcements/invites to education.

Talk to our professionals: demos, custom solutions, volume discounts.

Not ready to buy? Get Toad for Oracle a 3rd way ... try it free for 30 days.

Try Toad for Oracle 

Summary

In this article we learned about the NVL() and NVL2() functions and their usage with a few examples, useful for beginners who want to learn the advanced functions of PL/SQL programming.

Related Links

Blog: Allowing Only One NULL

Blog: How to use Quest® Toad® Data Point to cleanse, transform, save and retrieve data

Blog: Four functions every data preparation tool should offer

Tags: Toad for Oracle

Nisarg Upadhyay

Written by Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 11 years of experience with SQL Server administration and 2 years in managing oracle database. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com.