NVL2 provides a similar functionality as NVL function.
This function evaluates a column or expression in the following way:
- If the result is not NULL, it returns the second parameter that was given to NVL2 function.
- If the result is NULL, it returns the third parameter that was given to NVL2.
As you can tell, the main difference with NVL function is that if the result is not NULL, it doesn’t return original value, it returns a fixed value instead (specified as the second parameter in the NVL2 function).
Syntax
NVL2(expression , return_if_not_NULL , return_if_NULL)
Data types from expression and second and third parameter must be compatible.
Example
select name, surname, NVL2(hobbies, 'This user has hobbies','This user has no hobbies') from users;
With this query we want a report about our users, to know if they have a hobby or not.
We are not interested in the hobby itself, only in whether they have a hobby or not.