EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL nvl2
 

PL/SQL nvl2

Updated April 5, 2023

PL_SQL nvl2

 

 

Introduction to PL/SQL nvl2

In PL/SQL, nvl2() function is used to substitute the provided value or expression on the basis of null and not null value. It is basically an extension of the nvl() function of PL/SQL which takes 2 arguments. Function nvl2() takes 3 arguments, i.e. the original string or expression, other two being the substituted value based on the original string being null or not. Oracle provides the flexibility that the original string or expression can be of any data type and the substituted strings, i.e. other 2 arguments can also be of any data type except ‘LONG’. Oracle also performs specific actions if the 2 arguments except the original string are of different data type.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

Below given is the basic syntax of using the nvl2 function in a PL/SQL block:

NVL2 (expr1, expr2, expr3)

Where,

  • expr1: It is the string or the expression which is to be tested in the function nvl2.
  • expr2: It is the value which is to be returned as a result of the function if the expr1 is not null.
  • expr3: It is the value which is to be returned as a result of the function if the expr1 is null.

Return Type:

  • Function NVL2 returns the substituted value based on the string or expression provided.

How does nvl2 Work in PL/SQL?

Important points related to the working of nvl2 function in PL/SQL are given below:

1. Function nvl2 in PL/SQL extends the functionality of nvl function provided by Oracle.

2. Function nvl2 basically decides the value to be returned from the query based on the null and not null value.

3. It takes 3 parameters, first being the original string or expression, second being the substituted string if the provided expression is not null and third being the substituted string if the expression is null.

4. Original string ‘expr1’ can be of any data type.

5. Parameters ‘expr2’ and ‘expr3’ also can be of any data type except long.

6. In case when the ‘expr2’ and ‘expr3’ are of different data types:

  • If ‘expr2’ is of numeric data type, Oracle implicitly checks which argument is of high precedence data type and then converts either expr2 or expr3 to high precedence data type and returns that data type.
  • If ‘expr2’ is a character data type, Oracle before comparing, converts the ‘expr3’ to a data type of ‘expr2’ and returns the VARCHAR2 in character set of ‘expr2’. Conversion of data type does not takes place if ‘expr3’ is a null constant.

Following versions of Oracle PL/SQL supports the nvl2() function:

  • Oracle 12c
  • Oracle 11g
  • Oracle 10a
  • Oracle 9i
  • Oracle 8i

Examples of PL/SQL nvl2

Given below are the examples mentioned:

Example #1

Code:

SELECT
NVL2(NULL, 10, 98) as New_value
FROM
dual;

Output:

PLSQL nvl2 1

Explanation:

  • In the above example, 3 arguments are provided in the nvl2() function. The first argument is ‘NULL’ which is a null value. So, according to the nvl2() function property, it will return expr3 if the provided expression is NULL.
  • So, in the above function ‘expr3’ is ‘98’. Hence ‘98’ is printed in the column ‘new_value’ on the console to the user.

Example #2

Code:

SELECT
NVL2('hello', 10, 98) as New_value
FROM
dual;

Output:

PLSQL nvl2 2

Explanation:

  • In the above example, 3 arguments are provided in the nvl2() function. The first argument is ‘hello’ which is a character data type and is not null. So, according to the nvl2() function property, it will return expr2 provided in the function which is ‘10’ in the above example.
  • Hence ‘10’ is printed in the column ‘new_value’ on the console to the user.

Example #3

Code:

SELECT
NVL2(100, 'hello', 'hi') as string_value
FROM
dual;

Output:

PLSQL nvl2 3

Explanation:

  • In the above example, 3 arguments are provided in the nvl2() function. The first argument is 100 which is a numeric data type and is not null. So, according to the nvl2() function property, it will return expr2 provided in the function which is ‘hello’ in the above example.
  • Hence ‘hello’ is printed in the column ‘string_value’ on the console to the user.

Example #4

Code:

SELECT
NVL2('', 'this is not null value', 'this is a null value') as string_value
FROM
dual;

Output:

blank brackets ‘ ‘

Explanation:

  • In the above example, 3 arguments are provided in the nvl2() function. The first argument is the null value which is represented as blank brackets ‘ ‘. So, according to the nvl2() function property, it will return expr3 provided in the function if the expression ot the original string is null. So expr3 is ‘this is a null value’ in the above example.
  • Hence ‘this is a null value’ is printed in the column ‘string_value’ on the console to the user.

Example#5

Code:

SELECT
NVL2('hello hello', 'this is for not null value', '34') as string_value
FROM
dual;

Output:

3 arguments are provided

Explanation:

  • In the above example, 3 arguments are provided in the nvl2() function. The first argument is the ‘hello hello’ which is not a null value. As we can see above, that both the expr2 and expr3 are of different data types, so the Oracle performs the implicit conversions and according to that appropriate action would be taken. So, according to the nvl2() function property, it will return expr2 provided in the function if the expression ot the original string is not null. So expr2 is ‘this is for not null value’ in the above example.
  • Hence ‘this is for not null value’ is printed in the column ‘string_value’ on the console to the user.

Conclusion

Above description clearly explains what the nvl2 function is and how it works in a PL/SQL block. For a programmer, working on real databases, it is important to understand the concept of null and not null values and how to deal with them according to the resulting data from the tables in various situations clearly. Data represented to the user is according to the requirements of the user and showing null values is not an appropriate choice in many cases. So this function helps to deal with such situations.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL nvl2” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Triggers in PL/SQL
  2. PL/SQL Collections
  3. PL/SQL Data Types
  4. Loops in PL/SQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW