EMPTYIFNULL

Return an empty string if the input is NULL, otherwise return the input unchanged.

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

EMPTYIFNULL(expr)

Description

## Overview Returns the empty string `''` when the input is NULL and returns the input unchanged otherwise. Use this function to build safe string concatenations that do not produce NULL when one or more operands are missing, or to satisfy a downstream NOT NULL string column. EMPTYIFNULL is the inverse of NULLIFEMPTY. ## Behavior - Returns `''` if the input is NULL. - Returns the input unchanged otherwise. - Deterministic and side effect free. - Operates on the string value; no collation or locale is consulted. ## Compatibility - Semantically equivalent to `COALESCE(expr, '')`, but expressed as a single-argument helper that pairs with NULLIFEMPTY.

Parameters

Name	Type	Description
`expr`		Specifies the string expression to evaluate. Returns '' when the value is NULL; otherwise returns the value unchanged.

Examples

-- NULL becomes an empty string
SELECT EMPTYIFNULL(NULL);  -- ''

-- Non-NULL passes through
SELECT EMPTYIFNULL('hello');  -- 'hello'

-- Safe concatenation when middle_name may be NULL
SELECT first_name || ' ' || EMPTYIFNULL(middle_name) || ' ' || last_name AS full_name
FROM hr.catalog.employees;

-- Contrast with NULLIFEMPTY (they are inverses)
SELECT EMPTYIFNULL(NULL) AS e, NULLIFEMPTY('') AS n;  -- '' and NULL

-- Emit consistent output for a NOT NULL display column
SELECT EMPTYIFNULL(description) AS description FROM crm.catalog.customers;

Pitfalls

Replacing NULL with an empty string loses information: downstream queries can no longer tell 'unknown' from 'empty'. Use COALESCE with a sentinel value if the distinction matters.
EMPTYIFNULL does not trim or normalize whitespace. An input of a single space is returned unchanged; combine with TRIM if you want to collapse near-empty values.
String concatenation with `||` returns NULL if any operand is NULL in strict SQL semantics. Use EMPTYIFNULL on every nullable operand to avoid the full expression becoming NULL.
EMPTYIFNULL returns the empty string even for types that are implicitly cast to STRING. Consider whether the cast may introduce surprising representations (e.g., a NULL INTEGER becomes '' rather than '0').