This tutorial is based on examples to be easier to follow. The Oracle NVL allows replacing an empty (null) value with the second argument. Function Oracle NVL syntax is:
NVL (<value_1>, <value_2>)
Oracle NVL works in way that if the first column value is null then replace it with a second value as on the following example we are going to replace the null value with letter “Y“. In that example we will use an inner query to set the Oracle Dual column to NULL and that does NOT mean that you would need to use Oracle NVL function always with an inner query. This example below is written that way so it would to make the Oracle SQL example easier to follow and you wouldn’t have to create new Oracle tables to try this example. The Oracle Dual table is in present of all Oracle database versions and you can just do copy-paste and run it in your Oracle database.
SELECT NVL(my_value,'Y')
FROM (SELECT null AS my_value
FROM dual );
The second example will show you what does return Oracle NVL if the first argument is not NULL. You will find out it will ignore the second argument and it returns only the first column value. Take a look at the example below the first argument is named “my_value” and it has assigned the “X” character. The same character will be returned by the Oracle NVL function.
SELECT NVL(my_value,'Y')
FROM (SELECT 'X' AS my_value
FROM dual );
Sometimes you would need to use more than 2 arguments to compare with themselves and in that case we do recommend using function Oracle COALESCE. The Oracle COALESCE syntax is:
COALESCE ( <value_1>, <value_2>, <value_3>, <value_4>, ... )
The Oracle COALESCE function returns first not-empty (not null) value as on the following example:
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 example above has set only on the third column not null value and its name is “my_value3″. Since two first columns are left empty the Oracle Coalesce function ignores them and it returns the value of column my_value3 which is number “3“.
Another NVL functions is Oracle NVL2 and it lets you to return two following values depending on the first argument. If the first argument is NULL then Oracle NVL2 returns the 3rd value. Otherwise the function returns the 2nd value. The Oracle NVL2 syntax is:
NVL2 (<value_to_compare>, <returning_value_1>, <returning_value_2>)
Online tech support has prepared two following examples with Oracle NVL2. The 1st one has the 1st argument NULL and it will return the value from column my_value3.
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 second example with Oracle NVL2 has the first argument set to not null as number “0” (zero). This time the Oracle NVL2 works “reverse” way and returns the value from column my_value2. The my_value2 column has number “1” value.
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 difference between Oracle NVL and NVL2 is that NVL2 never returns the first declared value but only uses it to do comparison with NULL. Oracle NVL returns first or second value depending on which one is not null.
See Also:
Home Oracle Select Oracle NVL2 Oracle Decode