Oracle SQL_Null Related functions-1_NVL and NVL2


admin Functions, Oracle

Hello guys,
In this blog, we will talk about Null related functions. In Oracle documentation these functions are categorised under Single-Row Functions.

What are the Null related functions?

Null related functions are those functions which let user handle Null values in the query output.
It means if these are some null values in any column, we can replace null values with anything we want.
In order to learn the use of null related functions, we will start with NVL and then NVL2.

NVL Function
Syntax of NVL function is pretty simple.

NVL(expr1, expr2)

In the above syntax, expr1 and expr2 are columns from the table. In some cases, expr2 can be a char value.
If expr1 is null then NVL returns expr2 and if expr1 is not null then NVL returns expr1.
expr1 and expr2 can have any data type. Oracle engine can convert data type of expr2 implicitly to expr1 data type or sometimes it produces an error.
The implicit conversion is implemented as follows:
• If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
• If expr1 is numeric, then Oracle Database raise an error.

Let’s connect to HR schema of our Oracle database.

SELECT last_name, NVL(commission_pct, salary)  commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name;

LAST_NAME                 COMMISSION
------------------------- ----------
Baer                           10000
Baida                           2900
Banda                             .1
Bates                            .15
Bell                            4000
Bernstein                        .25
Bissot                          3300
Bloom                             .2
Bull                            4100

In the above sql, commission_pct and salary have the same data type in the table. So where commission_pct column does not have any value or have Null value, NVL function is retuning values from the Salary column of the table.

Let’s try this function with different data types.

SELECT last_name, NVL(commission_pct, 'Not Applicable') commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name;

Error starting at line : 1 in command -
SELECT last_name, NVL(commission_pct, 'Not Applicable') commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name
Error report -
ORA-01722: invalid number

Above SQL query will return an error because data type of expr2 is not matching with expr1 and Oracle engine is not able to convert data type implicitly.
To overcome this error, we need to add an addition conversion function to change the data type of commission_pct column to the data type of expr2.

SELECT last_name, NVL(to_char(commission_pct), 'Not Applicable') commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name;


LAST_NAME                 COMMISSION                              
------------------------- ----------------------------------------
Baer                      Not Applicable                          
Baida                     Not Applicable                          
Banda                     .1                                      
Bates                     .15                                     
Bell                      Not Applicable                          
Bernstein                 .25                                     
Bissot                    Not Applicable                          
Bloom                     .2                                      
Bull                      Not Applicable 

TO_CHAR() function is used to convert any data type to VARCHAR2 data type. I will publish a detailed blog separately on the conversion functions and introduce this function there.

Home work:
Create a table with some data where a column with VARCHAR2 data type has some NULL values and then use NVL function to display numeric values in that column.

NVL2 Function
Syntax of NVL2 function is little different than NVL function.
NVL2(expr1, expr2, expr3)

In the above syntax, expr1 must be a column name and expr2 and expr3 can be columns from the table or any values of any data type.
If expr1 is not null then NVL2 function will return expr2 and if expr1 is null then it will return expr3.
Data type conversion rules are same as NVL function.

Let’s do an example and see how it works.

SELECT last_name, salary,commission_pct,
NVL2(commission_pct, salary + (salary * commission_pct), salary) income
FROM employees
WHERE last_name like 'B%'
ORDER BY last_name;

------------------------- ---------- -------------- ----------
Baer                           10000                     10000
Baida                           2900                      2900
Banda                           6200             .1       6820
Bates                           7300            .15       8395
Bell                            4000                      4000
Bernstein                       9500            .25      11875
Bissot                          3300                      3300
Bloom                          10000             .2      12000
Bull                            4100                      4100

You can see in the output that where commission_pct (expr1) is null, Oracle database is showing Salary (expr3) as Income and where commission_pct (expr1) is not null, Oracle database is calculating expr2 and showing as Income.

That’s all for NVL and NVL2 function. Try more examples with different data types to have more understanding of NVL and NVL2 fuctions.
If you have any questions please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on social media.
For more updates join my facebook group.

Thank you,

Sharing is caring!

You May Also Like..



Hey alians, SUBSTR is pretty cool when it comes to extract a substring out of a string in SQL. SUBSTR […]



Hey alians, While working with the data, INSTR is a very important single row function that returns a numeric value. […]


Oracle_DUAL table

Hey geeks, DUAL table is a very important table available in the Oracle database. It gets created automatically during the […]

Leave a Reply

Your email address will not be published. Required fields are marked *