Oracle database

NVL2 oracle

Written by meda

NVL2 Function

NVL2 oracle function checks whether the data type of a column or expression is null or not.

The syntax for NVL2 oracle function is:

NVL2(original, ifnotnull,  ifnull)

If the value of the ‘original’  parameter is not null, the function returns the value of  the ‘ifnotnull’ parameter unless  the function returns the value of the ‘ifnull’ parameter.

Example:

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Fri Feb 25 17:13:52 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn mereba/mereba@orcl
Connected.

--- When the value of the 'original' parameter is not null
SQL> select NVL2('Mereba','MerebaTech',null) as value from dual;

VALUE
----------
MerebaTech

--- When the value of the 'original' parameter is null
SQL> select NVL2(null,'MerebaTech','Accounting') as value from dual;

VALUE
----------
Accounting

SQL>

Let’s also select from tbl_customer table , which has a column named swift_code and the column does not have data rows.

SQL> CONN mereba/mereba@orcl
Connected.
SQL> col cust_no format a30;
SQL> col customer_type format a10;
SQL> col cust_name format a23;
SQL> col country  format a15;
SQL> col swift_code format a14;

SQL> SELECT cust_no,NVL2(swift_code,'ifnotnull','ifnull')swift_code,cust_name,country FROM TBL_CUSTOMER;

   CUST_NO SWIFT_CODE     CUST_NAME               COUNTRY
---------- -------------- ----------------------- ---------------
########## ifnull         Messi John Dave         USA
########## ifnull         Mereba Technology       UK
########## ifnull         Swiss Bank              Switzerland

SQL>

As you can see in the query above, since the swift_code column is null in the database, the NVL2 function returns ‘ifnull‘ value.