NVL & NVL2 in Oracle - Comparison Function

Hi, Today I discuss about important topic - NVL & NVL2

NVL

The Oracle NVL() function allows you to replace null with a more meaningful alternative in the results of a query.

Syntax:


NVL(e1, e2)

If e1 evaluates to null, then NVL() function returns e2. If e1 evaluates to non-null, the NVL() function returns e1.

Example1:

 SELECT   NVL(50,100) FROM   dual;

It will return 50 because first argument (e1=50) is not NULL.

Example2:

SELECT   NVL(NULL,'FALSE') FROM   dual;

It will return 100 because first argument is NULL.

Example3:

SELECT
  student_id,NVL(mname, fname|| ' '  ||  lname) AS Full_Name
FROM students;

NVL2

The Oracle NVL2() function is an extension of the NVL() function with different options based on whether a NULL value exists.

The Oracle NVL2() function accepts three arguments. If the first argument is not null, then it returns the second argument. In case the second argument is null, then it returns the third argument.

Syntax:

NVL2(e1,e2,e3)

Example:

SELECT   NVL2(NULL, 1, 2) 
FROM   dual;

It will return 2 because first argument is NULL.

Example:

SELECT
  student_id,NVL2(mname,fname|| ' '|| mname || ' ' ||  lname, fname|| ' '  ||  lname) AS Full_Name
FROM students;

Thank you for reading .Feel Free to ask you Query in comment section.

0 comments