NVL2 is the second null function in the series of Null functions. We can say it’s a null function like NVL but on steroids as NVL2 expands the functionality of NVL function. NVL2 function lets you substitute the values when a null value or a non-null value is encountered.
Syntax
NVL2 (Expression, value1, value2)
Unlike NVL null function NVL2 function has 3 parameters. Expression, value1 and value 2.
First parameter Expression can either be column name or an arithmetic expression whose result will be evaluated, whereas the second parameter is the value which will be returned if the first parameter value is Not Null. Furthermore if the first parameter value is Null the NVL2 returns the third parameter.
In Simple words if the data returned from the first parameter is Not Null then value from the second parameter will be displayed otherwise value from the third parameter will be returned.
Example
SELECT commission_pct, NVL2 (commission_pct, ‘Not Null’, 0) FROM employees WHERE salary>13000;
On execution this query will return values in two columns where first column will display the actual values from commission pct column and second will display the string Not Null (where some actual values exist in commission pct) otherwise it will display 0 in place of Null.
Now if you want to display the actual value of commission pct column instead of Not Null string then you can simply write the column name which is commission pct at the place of this string.
Let me show you how
SELECT commission_pct, NVL2(commission_pct, commission_pct,0) FROM employees WHERE salary>13000;
Now, you have to comment on my YouTube or tweet me on my twitter and tell me – According to you what are the applications or uses of this particular function NVL2? Where and how can you use this function? And if you have a crazy query with this NULL function NVL2 you can also share that in the comment section below.
That’s all guys. Hope this article was useful. Kindly please share it on your social network and help me reach out to more people. Thanks and have a great day!
