NVL and NVL2 function in SQL

NVL() Function:

Ø NVL() function is used to replace the NULL values by Specified values.

Syntax:

1.     NVL(Expression1, Expression2)

Ø The NVL() function accepts two arguments, If Expression1 evaluates NULL, Then NVL() function return Expression2.

Ø If Expression1 evaluates NOT NULL, Then NVL() function return Expression1.

Table: Customers

Cust_Id	Cust_Name	Country	Status
1001	Tae	US	Active
1002	Thara	India	InActive
1003	Jimin	US
1004	Tanie

Example: 1

1. Select Cust_id, Cust_Name, Nvl(Status,’Unknown’) Status

   from customers

   Where Cust_id=1001;

Output:

Cust_Id	Cust_Name	Status
1001	Tae	Active

 Example: 2

1. Select Cust_id, Cust_Name, Nvl(Status,’Unkonwn’) Status

   from customers

   Where Cust_id=1004;

Output:

Cust_Id	Cust_Name	Status
1004	Tae	Unknown

NVL2() Function:

Ø NVL2() function is the extension of NVL() finction.

Syntax:

1.     NVL2(Expression1, Expression2, Expression3)

   Ø NVL2() function accepts Three arguments.

   Ø If Expression1 evaluates NOT NULL, Then NVL2() function returns Expression2.

   Ø If Expression1 evaluates NULL, Then NVL2() function returns Expression3.

Table: Books

Book_Name	Pages	Price	Discount
SQL	500	500	200
PL/SQL	600	1000	300
DBMS	800	1000

Example: 1

1.   Select Book_Name, Nvl2(Discount, Price – Discount, price) Book_Price

     From Books

     Where Book_Name=’DBMS’;

Output:

Book_Name	Book_Price
DBMS	1000

 Example: 2

1. Select Book_Name, Nvl2(Discount, Price – Discount, price) Book_Price

   From Books;

Output:

Book_Name	Book_Price
SQL	300
PL/SQL	700
DBMS	1000

 

 

Share: