Teradata NVL and NVL2 Function, Syntax and Examples

  • Post author:
  • Post last modified:September 9, 2020
  • Post category:General
  • Reading time:5 mins read

The Teradata is one of the widely used MPP relational database systems. Most of the organizations are using Teradata for their high-performance servers such as analytics, back-end systems for reporting servers, etc. Teradata is used with many heterogeneous data sources, you may get lots of junk and null values. You should have a mechanism to deal with such a data. In this article, we will check such a null handling functions. i.e. Teradata NVL function, and NVL2 functions with some examples.

Teradata NVL Function

 Teradata NVL functions replaces a NULL value with a numeric or a string value. In other word, the NVL function replaces NULL values with a replacement string that you provide as a function argument. The NVL function works on data from an expression or a value from input column. This function returns the first argument if it is not null, otherwise the second argument.

The Teradata NVL function is equivalent to Teradata COALESCE function. The function will return first non-null value, it won’t evaluate remaining expressions in the list. In case, if the input values and replacement value is null then obviously results would be null value.

Read:

Teradata NVL Syntax

Below is the NVL syntax:

NVL (exp1, exp2);

Returns ‘exp1’ if not null otherwise ‘exp2’ is returned.

Teradata NVL Function Example

Below is the example on usage of Teradata NVL function:

SELECT qty, 
       NVL (qty, 0) new_qty 
FROM   (SELECT NULL AS qty) AS a;

Here is the output of NVL function:

qty new_qty
<null> 0

Teradata NVL2 Function

Teradata NVL2 Function returns the second argument if the first argument is not null, otherwise it returns the third argument. Teradata NVL2 is shorthand for the Teradata CASE expression function.

Read:

Teradata NVL2 Syntax

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

Below is the NVL2 syntax:

NVL (exp, exp1, exp2);

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

Teradata NVL2 Function Examples

Below is the example to demonstrate Teradata NVL and NVL2 functions:

SELECT price, 
       curr, 
       NVL2(price, 0, 1)  new_price, 
       NVL2(curr, 1, 0)  curr1 
FROM   (SELECT NULL  price, 
               1    curr) a;

Here is the output of Teradata NVL2 function:

price curr new_price curr1
<null> 1 1 1

Hope this helps ?

Tags: