Null Value Functions

Contact Us or call 1-877-932-8228
Null Value Functions

Null Value Functions

The NVL function is used to replace a null with a string. The NVL2 function returns one value if a given expression is null and a different value if the expression not null. (The value returned needs to be of the same type regardless of whether the expression is null or not null).

Code Sample:

SQL-Functions-Non-Character/Demos/nvl_and_nvl2.sql
SELECT 
	first_name, 
	last_name, 
	salary,  
	NVL(commission_pct,0) 
FROM employees 
WHERE last_name like 'K%';

SELECT 
	first_name, 
	last_name, 
	salary,  
	NVL(TO_CHAR(commission_pct,'0.99'),
		'No Commission'
	) 
FROM employees 
WHERE last_name like 'K%';

SELECT 
	first_name, 
	last_name, 
	salary,  
	NVL2(commission_pct,
		'Has a Commission',
		'No Commission'
	) 
FROM employees 
WHERE last_name like 'K%';

In the first example, a NULL in the employee commission_pct is replaced with a zero. The second query returns either a formatted commission percent or the string literal "No Commission." The third example either returns a literal indicating that the record has a commission, or one indicating that there is no commission.

Next