What are the NVL and the NVL2 functions in Oracle? How do they differ?

Question

I am looking for the detail answer with simple examples of these functions.

Answer 1

NVL checks if first argument is null and returns second argument:

select nvl(null, 'arg2') from dual

in this example result will be: arg2;

select nvl('arg1', 'arg2') from dual

and in this one: arg1;

NVL2 has different logic. If first argument is not null then NVL2 returns second argument, but in other case it will return third argument:

select nvl2('arg1', 'arg2', 'arg3') from dual

Result: arg2

select nvl2(null, 'arg2', 'arg3') from dual

Result: arg3

Answer 2

Nvl(arg1,arg2) nvl is used for converting null values. In nvl if argument 1 is null then it returns argument 2 but argument 1 is not null it returns itself. In nvl2 (arg1,arg2,arg3) in nvl2 it converts any number into according to given number with null also . If arg 1 that's given number is null then it returns arg3 And on the other hand if it is not null it returns argument 2