Redshift NVL and NVL2 Functions with Examples

  • Post author:
  • Post last modified:March 13, 2023
  • Post category:Redshift
  • Reading time:4 mins read

Like many other relational database or data warehouse appliances, Redshift supports NVL and NVL2 functions. These functions are mainly used to handle the null values in Redshift tables.

Redshift NVL Function

An NVL expression returns the value of the first expression in the list that is not null. The NVL function replaces a NULL value with a replacement string that you provide in the function as argument. This function returns the first argument if it is not null, otherwise the second argument.

The Redshift NVL function is equivalent to the Redshift SQL COALESCE function. The Function will return the first non-NULL value. If all expressions are null, the result is null. When a non-null value is found, the remaining expressions in the list are not evaluated.

Redshift NVL Syntax

Below is the NVL syntax:

NVL (exp,replacement-exp);

Returns ‘exp’ if not null otherwise ‘replacement-exp’ is returned.

Redshift NVL Function Example

Below is the example on usage of Redshift NVL function:

training=# select price, NVL(price::int, 0) as value from (select NULL as price) a; 
 price | value 
-------+------- 
 | 0 
(1 row)

training=# select price, NVL(price::numeric(9,3), 0.0) as value from (select NULL as price) a; 
 price | value 
-------+------- 
 | 0.0 
(1 row)

Redshift NVL2 Function

Redshift NVL2 Function returns the second argument if the first argument is not null, otherwise it returns the third argument. Redshift NVL2 is short hand for the Redshift CASE expression.

Read:

Redshift NVL2 Syntax

Redshift NVL2 takes three parameters. An expression, a replacement value 1 and replacement value 2.

Below is the NVL2 syntax:

NVL (exp, replacement-exp1,replacement-exp1);

NVL2 works like IF statement. Returns ‘replacement-exp1’ if ‘exp’ is not null, otherwise ‘replacement-exp2’ is returned. 

Redshift NVL2 Function Examples

training=# select price,curr, NVL2(price::numeric(9,3), 0.0,1.0) as value, NVL2(curr::int,1,0) as curr1 from (select NULL as price,1 as curr) a; 
 price | curr | value | curr1 
-------+------+-------+------- 
 | 1 | 1.0 | 1 
(1 row)

Related Articles

Tags: