How To Use Oracle NVL

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 );

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 );

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 );

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) ;

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) ;

See Also:
Oracle NVL2 Oracle Select Home