ORACLE SQL NVL2 - Online Tech Support

This page is based on examples to be easier to follow. The Oracle NVL2 function returns two different results depending on the first input value. Take a look at the following Oracle NVL2 syntax. Oracle NVL2 returns the second parameter (return_when_NOT_NULL) when the input value (input_value) is NOT empty and otherwise the function returns the third parameter (return_when_NULL).

NVL2 (<input_value>,<return_when_NOT_NULL>,<return_when_NULL>);

Please don not get confused with Oracle NVL function that has only two values to compare and it returns first not null value. The following Online Tech Support example shows how the NVL2 function works when the first value is NULL.

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

The SQL query returned number “2” as it has been declared as the 3rd value (MY_VALUE3) in the function. The Oracle NVL2 returns the last value in the function when the first input value is empty.

The next Oracle NVL2 example is with the same NVL2 returning values but the first input value is set to 0 (or not null).

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

The SQL query output returned value number “1“. As the first input value is NOT empty then function returns the second declared value that is number “1” (MY_VALUE2).

The third example demonstrates that you also can use a text value as for example “Hello“. Oracle NVL2 can be used with most built-in data types and it only checks if the value is NULL or NOT. The example above has first input value not null and the function returns again the second value number “1” as on the last example.

SELECT NVL2(my_value1,my_value2,my_value3) AS nvl2_result
  FROM (SELECT 'Hello' AS my_value1,1 AS my_value2, 2 AS my_value3
          FROM dual) ;

In short: Oracle NVL2 checks if the 1st parameter is null or not and return the 2nd parameter when it is NOT NULL otherwise it returns your 3rd parameter. All parameters are declared by you depending what do you want to be returned.