Oracle NVL2 Function: Usage & Examples

In Oracle, the NVL2() function is used to check if an expression is NULL. If it is not NULL, it will return the value of the first expression. If it is NULL, it will return the value of the second expression. The syntax is as follows:

NVL2(expr1, expr2, expr3)

If expr1 is the expression to be checked, return the value of expression expr3 if it is NULL, otherwise return the value of expression expr2.

For example, suppose there is a table containing student grade information, in which there may be some NULL values. We can use the NVL2() function to handle these NULL values, as follows:

SELECT student_id, NVL2(english_score, 'Pass', 'Fail') as english_result
FROM student_scores;

In the example above, return ‘Pass’ if english_score is not NULL, otherwise return ‘Fail’.