Substituting NULL in Oracle using NVL...

LINK: http://www.techonthenet.com/oracle/functions/nvl.php

Oracle/PLSQL: NVL Function

---

In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

The syntax for the NVL function is:

NVL( string1, replace_with )

string1 is the string to test for a null value.

replace_with is the value returned if string1 is null.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Example #1:

select NVL(supplier_city, 'n/a')
from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.

Example #2:

select supplier_id,
NVL(supplier_desc, supplier_name)
from suppliers;

This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.

Example #3:

select NVL(commission, 0)
from sales;

This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field.

Frequently Asked Questions

---

Question: I tried to use the NVL function through VB to access Oracle DB.

To be precise,

select NVL(DIstinct (emp_name),'AAA'),................ from.................

I got an oracle error when I use distinct clause with NVL, but when I remove distinct it works fine.

Answer: It is possible to the use the DISTINCT clause with the NVL function. However, the DISTINCT must come before the use of the NVL function. For example:

select distinct NVL(emp_name, 'AAA')
from employees;

Hope this helps!

---

Question: Is it possible to use the NVL function with more than one column with the same function call? To be clear, if i need to apply this NVL function to more than one column like this:

NVL(column1;column2 ...... , here is the default value for all )

Answer: You will need to make separate NVL function calls for each column. For example:

select NVL(table_name, 'not found'), NVL(owner, 'not found')
from all_tables;