How To Use Oracle NVL

How To Use Oracle NVL

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This tutorial page is based on simple examples to make it easier to follow. Function Oracle NVL allows to replace empty (null) values with the second argument. For example if the first column value is null then NVL in Oracle replaces it with “Y” as in the following Oracle database management example. Our Oracle DBA is using inner query to set table dual column value NULL.

SELECT NVL(my_value,'Y') 
  FROM (SELECT null AS my_value 
          FROM dual );

dba oracle dba database management system oracle performance tuning oracle nvl oracle nvl in oracle the stock market plumbing oracle coalesce oracle nvl2 oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

If the first column value is not empty then function NVL in Oracle will return the first column value.

SELECT NVL(my_value,'Y') 
  FROM (SELECT 'X' AS my_value 
          FROM dual );

dba oracle dba database management system oracle performance tuning oracle nvl oracle nvl in oracle the stock market plumbing oracle coalesce oracle nvl2 oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

When you want to use more than one column and to compare themselves our Oracle DBA recommends you to use function Oracle COALESCE. The function COALESCE in Oracle lets you to use unlimited amount of columns and returns the first not-empty (not null) value as the follow:

SELECT COALESCE(my_value1, my_value2, my_value3,'Y') 
  FROM (SELECT NULL AS my_value1, 
               NULL AS  my_value2, 
                  3 AS my_value3 
          FROM dual );

dba oracle dba database management system oracle performance tuning oracle nvl oracle nvl in oracle the stock market plumbing oracle coalesce oracle nvl2 oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

The another useful function is Oracle NVL2 and it lets you to return two different results depending on the first value. If the first value is null then function NVL2 in Oracle returns the 3rd value declared in it. Otherwise the function Oracle NVL2 returns the 2nd value. Oracle DBA has prepared 2 following examples where the first example is with Oracle NVL2 and the first column has null value.

SELECT NVL2(my_value1,my_value2,my_value3)
 FROM (SELECT NULL AS my_value1,1 AS my_value2, 2 AS my_value3
          FROM dual) ;

dba oracle dba database management system oracle performance tuning oracle nvl oracle nvl in oracle the stock market plumbing oracle coalesce oracle nvl2 oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

And the next Oracle database management system example is with function NVL2 Oracle and the first value is not null. And the second value as 1 will be returned by the function.

SELECT NVL2(my_value1,my_value2,my_value3)
  FROM (SELECT 0 AS my_value1,1 AS my_value2, 2 AS my_value3
          FROM dual) ;

dba oracle dba database management system oracle performance tuning oracle nvl oracle nvl in oracle the stock market plumbing oracle coalesce oracle nvl2 oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

See Also:
Oracle NVL2 Oracle Select Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*