CLEAN_SPACES

Collapse runs of whitespace into a single space and trim leading/trailing whitespace.

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

CLEAN_SPACES(str)

Description

## Overview Normalizes whitespace in the input string by collapsing every run of consecutive whitespace characters into a single ASCII space and then trimming any resulting leading or trailing space. Use this function to clean CSV imports, normalize user-entered strings before comparison, or canonicalize data for deduplication. ## Behavior - Returns NULL if the input is NULL. - Treats all Unicode whitespace (spaces, tabs, newlines, non-breaking spaces, CJK spaces) as whitespace. - Replaces any run of whitespace with a single ASCII space (U+0020). - Trims leading and trailing whitespace. - Leaves non-whitespace characters unchanged. - Deterministic and side effect free. ## Compatibility - Equivalent to the common idiom `TRIM(REGEXP_REPLACE(str, '\s+', ' '))` but treats all Unicode whitespace, including non-breaking space.

Parameters

Name	Type	Description
`str`		Specifies the input string. Any Unicode whitespace run is collapsed to a single ASCII space.

Examples

-- Collapse multiple spaces
SELECT CLEAN_SPACES('hello   world');  -- 'hello world'

-- Trim leading and trailing spaces
SELECT CLEAN_SPACES('  hello  ');  -- 'hello'

-- Handle tabs and mixed whitespace
SELECT CLEAN_SPACES('a  b	c');  -- 'a b c'

-- Normalize a column before comparison
SELECT customer_id FROM crm.catalog.customers
WHERE CLEAN_SPACES(LOWER(full_name)) = 'jane doe';

-- NULL propagation
SELECT CLEAN_SPACES(NULL);  -- NULL

Pitfalls

Tabs, newlines, and other control characters are all collapsed to a single space. If you need to preserve line breaks (for example, in multi-line text), apply a line-by-line transform instead.
Non-breaking spaces and zero-width whitespace are removed or collapsed, which may change visible formatting. Use a targeted REPLACE if you need to keep them.
CLEAN_SPACES changes string length and byte layout. Downstream code that relies on exact character positions (for example, fixed-width parsers) must be updated.
This function is Unicode-aware but not locale-aware. It does not understand grapheme clusters or bidirectional text.