top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is the use of NVL2 in Oracle?

+2 votes
432 views
What is the use of NVL2 in Oracle?
posted Aug 10, 2015 by Suchithra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

ORACLE/PLSQL: NVL2 FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL NVL2 function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.

SYNTAX

The syntax for the NVL2 function in Oracle/PLSQL is:

 NVL2( string1, value_if_not_null, value_if_null )

Parameters or Arguments

string1

The string to test for a null value.

value_if_not_null

The value returned if string1 is not null.

value_if_null

The value returned if string1 is null.

APPLIES TO

The NVL2 function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle NVL2 function examples and explore how to use the NVL2 function in Oracle/PLSQL.

For example:

select NVL2(supplier_city, 'Completed', 'n/a')
from suppliers;
The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the 'Completed'.

Another example using the NVL2 function in Oracle/PLSQL is:

  select supplier_id,    NVL2(supplier_desc, supplier_name, supplier_name2) from suppliers;

This SQL statement would return the supplier_name2 field if the supplier_desc contained a null value. Otherwise, it would return the supplier_name field.

answer Aug 10, 2015 by Shivaranjini
...