How To Use Oracle NVL2 Function
  Home
 

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

online tech support computer help computer technician computer problems oracle database oracle nvl2 oracle nvl2 in oracle sql nvl2 oracle sql nvl2 in oracle sql database programming database oracle nvl oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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

online tech support computer help computer technician computer problems oracle database oracle nvl2 oracle nvl2 in oracle sql nvl2 oracle sql nvl2 in oracle sql database programming database oracle nvl oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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

online tech support computer help computer technician computer problems oracle database oracle nvl2 oracle nvl2 in oracle sql nvl2 oracle sql nvl2 in oracle sql database programming database oracle nvl oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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.



See Also:
Home Oracle Select Oracle NVL Oracle Decode

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*