What is the purpose of the Oracle NVL2 function?
The Oracle NVL2 function is used to evaluate a condition and return one of two different values based on the result.
The syntax for the NVL2 function is as follows:
NVL2(expression1, expression2, expression3)
- expression1 is an expression used to determine if it is empty.
- expression2 will return its value if expression1 is not empty.
- The expression3 is a condition that returns the value of expression3 if expression1 is empty.
The purpose of the NVL2 function is to return the value of expression2 when expression1 is not empty, and to return the value of expression3 when expression1 is empty. This function is commonly used for handling or replacing null values in query statements or data processing.
For example, let’s say there is a table called student, with a field age that represents the age of the student. If the age of a student is null, you can use the NVL2 function to replace it with a default value:
SELECT NVL2(age, age, ‘Unknown’) FROM student;
This query will return the students’ age, if the age is empty, then ‘unknown’ will be returned.
