NULLIFEMPTY

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

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

NULLIFEMPTY(expr)

Description

## Overview Returns NULL when the input string is exactly the empty string, and returns the input unchanged otherwise. Use this function when ingesting CSV, JSON, or form data where missing fields arrive as empty strings and should be promoted to NULL for downstream analytics. NULLIFEMPTY is the inverse of EMPTYIFNULL. ## Behavior - Returns NULL if the input is NULL or is exactly `''`. - Returns the input unchanged otherwise. - Whitespace-only strings are NOT treated as empty; combine with TRIM if needed. - Deterministic and side effect free. - Operates on the string value; no collation or locale is consulted. ## Compatibility - Semantically equivalent to `NULLIF(expr, '')`, but expressed as a single-argument helper for readability.

Parameters

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

Examples

-- Empty string becomes NULL
SELECT NULLIFEMPTY('');  -- NULL

-- Non-empty string is returned unchanged
SELECT NULLIFEMPTY('hello');  -- 'hello'

-- Whitespace is not empty
SELECT NULLIFEMPTY(' ');  -- ' '

-- NULL input stays NULL
SELECT NULLIFEMPTY(NULL);  -- NULL

-- Normalize missing middle names in an import
SELECT customer_id, NULLIFEMPTY(middle_name) AS middle_name
FROM crm.stage.imports;

-- Combine with TRIM to also treat whitespace-only strings as missing
SELECT NULLIFEMPTY(TRIM(raw_value)) AS normalized
FROM crm.stage.imports;

Pitfalls

NULLIFEMPTY only compares against the empty string. A single space is not treated as empty. Wrap the argument in TRIM if whitespace-only values should also become NULL.
NULLIFEMPTY is a single-argument function and differs from the two-argument NULLIF: `NULLIF(a, b)` returns NULL only when `a = b`, whereas `NULLIFEMPTY(a)` returns NULL only when `a = ''`.
The function operates on the string value, not on its visual representation. Zero-width or control characters count as non-empty content even if they render as blank in a terminal.
Applying NULLIFEMPTY in a CASE expression is redundant; the function already handles the NULL and empty cases in one call.