Using NULL Value Functions

Oracle Database XE provides functions that you can use in your SQL statements to work with NULL values. For example, you can substitute a different value if value in a column of a table is NULL.

Example: Using the SQL NVL Function shows the use of the SQL NVL function. This function substitutes the specified value when a NULL value is encountered.

Using the SQL NVL Function

-- use the NVL function to substitute 0 for a NULL value in commission_pct
SELECT commission_pct, NVL(commission_pct, 0) FROM employees;

-- use the NVL function to substitute MISSING for a NULL value in phone_number
SELECT phone_number, NVL(phone_number, 'MISSING') FROM employees;

Example: Using the SQL NVL2 Function shows the use of the SQL NVL2 function. This function returns the second specified expression when the first expression is not NULL. If the first expression is NULL, the third expression is returned.

Using the SQL NVL2 Function

-- use the NVL2 function to return salary + (salary * commission_pct) 
-- if commission_pct is not NULL; otherwise, if commission_pct is NULL, 
-- then return salary
SELECT employee_id , last_name, salary, 
  NVL2(commission_pct, salary + (salary * commission_pct), salary) income
  FROM employees;