Redshift NVL and NVL2 Functions – Syntax and Examples

  • Post author:
  • Post last modified:September 19, 2022
  • Post category:Redshift
  • Reading time:6 mins read

In my other post, we have discussed how to handle NULL values in Redshift using NULL handling functions. In this article, we will check couple of NULL handling functions such as Redshift NVL and NVL2 functions in details with syntax, usage and few examples.

Redshift NVL and NVL2 Functions
Redshift NVL and NVL2 Functions

Redshift NVL and NVL2 Functions

Similar to many other relational database or data warehouse appliances, Redshift supports NVL and NVL2. These functions are mainly used to handle the null values in Redshift tables. For example, replace NULL values with any readable value during the data presentation such as reporting queries.

Now, let us check these two functions in details:

Redshift NVL Function

The Redshift NVL function returns the values of the first non-null 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 an argument. In conclusion, 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 The COALESCE 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

Following is the Redshift NVL syntax:

NVL (exp,replacement-exp);

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

Redshift NVL Function Example

Following examples uses NVL function to replace NULL with 0 and 0.0 respectively.

select amt, NVL(amt::int, 0) as value from (select NULL as amt) a; 
 amt   | value 
-------+------- 
 | 0 
(1 row)

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

Redshift NVL2 Function

Similarly, Amazon Redshift NVL2 Function returns the second argument if the first argument is not null, otherwise it returns the third argument. Redshift NVL2 is shorthand for the CASE expression. The NVL2 is equivalent to IF statements found in the database such as SQL Server and Azure Synapse.

Redshift NVL2 Syntax

Following is the Redshift NVL2 syntax:

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

Redshift NVL2 takes three parameters. An expression, a replacement exp1 and replacement exp2. The NVL2 works like IF statement. Returns ‘replacement-exp1’ if ‘exp’ is not null, otherwise ‘replacement-exp2’ is returned.

Redshift NVL2 Function Example

Following examples uses NVL2 function to replace NULL values.

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

Related Articles

Hope this helps 🙂

Tags: , , ,