NVL

Returns the value of the first non-null expression in the list.

Behavior Type

Immutable

Syntax

NVL ( expression1 , expression2 );

Parameters

  • If expression1 is null, then NVL returns expression2.
  • If expression1 is not null, then NVL returns expression1.

Notes

  • COALESCE is the more standard, more general function.
  • NVL is equivalent to COALESCE except that NVL is called with only two arguments.
  • The arguments can have any data type supported by Vertica.

  • Implementation is equivalent to the CASE expression:

    CASE WHEN expression1 IS NULL THEN expression2 
ELSE expression1 END;

Examples

expression1 is not null, so NVL returns expression1:

SELECT NVL('fast', 'database'); 
nvl  
------
 fast
(1 row)

expression1 is null, so NVL returns expression2:

SELECT NVL(null, 'database');   
nvl    
----------
 database
(1 row)

expression2 is null, so NVL returns expression1:

SELECT NVL('fast', null); 
nvl  
------
 fast
(1 row)

In the following example, expression1 (title) contains nulls, so NVL returns expression2 and substitutes 'Withheld' for the unknown values:

SELECT customer_name, NVL(title, 'Withheld') as title
FROM customer_dimension
ORDER BY title;
     customer_name      | title 
------------------------+-------
 Alexander I. Lang      | Dr.
 Steve S. Harris        | Dr.
 Daniel R. King         | Dr.
 Luigi I. Sanchez       | Dr.
 Duncan U. Carcetti     | Dr.
 Meghan K. Li           | Dr.
 Laura B. Perkins       | Dr.
 Samantha V. Robinson   | Dr.
 Joseph P. Wilson       | Mr.
 Kevin R. Miller        | Mr.
 Lauren D. Nguyen       | Mrs.
 Emily E. Goldberg      | Mrs.
 Darlene K. Harris      | Ms.
 Meghan J. Farmer       | Ms.
 Bettercare             | Withheld
 Ameristar              | Withheld
 Initech                | Withheld
(17 rows)

See Also