NVL Function in Oracle SQL – PLSQL

Oracle SQL / PLSQL uses NVL function to substitute a value whenever a NULL is encountered.

Syntax for the NVL function in Oracle SQL / PLSQL is:

SELECT NVL(string1, replace_with)
FROM table_name;

  • ‘string1’ is the field or column for testing NULL value
  • ‘replace_with’ is the string with which the NULL values will be substituted with.

Let’s take an example for understanding:

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000
104 Emp D 30000
105 Emp E 32000

Example 1: Using Oracle SQL / PLSQL NVL Function with numbers and string

Suppose we write our query as:

SELECT employee_id
       ,employee_name
       ,salary
       ,NVL(department,'No Department')
       ,NVL(commission,0)
FROM employee;

We will get the following output:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 No Department 0
104 Emp D 30000 No Department 0
105 Emp E 32000 No Department 0

Using the Oracle SQL /PLSQL NVL Function in example above we have substituted a string and number type in ‘department’ and ‘commission’ columns respectively values ‘No Department’ and ‘0’.


Tagged , , , . Bookmark the permalink.