Oracle SQL / PLSQL uses NVL function to substitute a value whenever a NULL is encountered.
We Will be looking at:
Syntax for the NVL function in Oracle SQL / PLSQL is:
SELECT NVL(string1, replace_with)
- ‘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:
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:
|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’.