NVL function to replace null values with a default value - MaxCompute

If value is NULL, returns default_value. Otherwise, returns value.

NVL is equivalent to COALESCE(value, default_value) with two arguments.

Syntax

nvl(T <value>, T <default_value>)

Parameters

Parameter	Required	Description
`value`	Yes	The value to evaluate. `T` can be any data type supported by MaxCompute.
`default_value`	Yes	The value returned when `value` is NULL. Must be the same data type as `value`.

Return value

If value is not NULL, returns value.
If value is NULL, returns default_value.
If both value and default_value are NULL, returns NULL.

Examples

Sample data

Create a table and insert test data:

CREATE TABLE nvl_test (
  c1 STRING,
  c2 BIGINT,
  c3 DATETIME
);

INSERT INTO nvl_test VALUES
  ('aaa', 23, '2024-01-11 00:00:00'),
  ('bbb', NULL, '2024-01-12 08:00:00'),
  (NULL, 20, '2024-01-13 05:00:00'),
  ('ddd', 25, NULL);

Replace NULL values with defaults

Replace NULL in c1 with '00000', NULL in c2 with 0, and NULL in c3 with '-':

SELECT nvl(c1, '00000'), nvl(c2, 0), nvl(c3, '-') FROM nvl_test;

Output:

+-------+-----+---------------------+
| _c0   | _c1 | _c2                 |
+-------+-----+---------------------+
| aaa   | 23  | 2024-01-11 00:00:00 |
| bbb   | 0   | 2024-01-12 08:00:00 |
| 00000 | 20  | 2024-01-13 05:00:00 |
| ddd   | 25  | -                   |
+-------+-----+---------------------+

Related functions

COALESCE: Returns the first non-NULL value from multiple arguments. Use COALESCE when you need more than two fallback values.
Other functions