• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / function / NVL Function in Oracle SQL – PLSQL

NVL Function in Oracle SQL – PLSQL

November 6, 2012 by techhoneyadmin

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’.


Filed Under: function Tagged With: how to replace NULL values in oracle sql, NVLPLSQL, oracle sql NVL function syntax and example, oracle sql plsql nvl function

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in