TRIM

Remove leading and trailing whitespace from the input string.

Category: stringReturns: VARCHARDialect: Standard

Syntax

TRIM(str)

Description

## Overview Removes leading and trailing whitespace characters from the input string and returns the result. This is one of the most common string hygiene operations: incoming text from forms, CSV files, spreadsheets, and REST payloads routinely carries stray spaces, tabs, or newlines that break joins, deduplication, and equality checks. TRIM is the first line of defence. TRIM in this form strips whitespace only. To remove a different set of characters, use BTRIM with a custom character set, or LTRIM/RTRIM to restrict the operation to one end. ## Behavior - Returns NULL when the input is NULL. - Returns an empty string when the input is empty or consists only of whitespace characters. - Whitespace is defined as the standard Unicode whitespace set: space (U+0020), tab (U+0009), line feed (U+000A), carriage return (U+000D), form feed (U+000C), vertical tab (U+000B), and the non-breaking space (U+00A0). - Interior whitespace between non-whitespace characters is preserved exactly. - Operates on UTF-8 code points, so multi-byte characters adjacent to whitespace are kept intact. - Equivalent to BTRIM(str) called without a character set argument. ## Compatibility - Conforms to the SQL standard TRIM(str) form. The more general standard form TRIM([LEADING | TRAILING | BOTH] [chars FROM] str) is also supported and maps to LTRIM/RTRIM/BTRIM.

Parameters

Name	Type	Description
`str`		Specifies the input string from which leading and trailing whitespace is removed.

Examples

-- Trim both ends
SELECT TRIM('  hello  ');  -- 'hello'

-- Clean a user-entered column before storing
SELECT TRIM(display_name) AS display_name_clean
FROM retail.customers.profiles;

-- NULL propagates
SELECT TRIM(CAST(NULL AS VARCHAR));  -- NULL

-- Empty string stays empty
SELECT TRIM('');  -- ''

-- Interior whitespace is preserved
SELECT TRIM('  hello   world  ');  -- 'hello   world'

-- Combine with LOWER to build a canonical email key
SELECT LOWER(TRIM(email)) AS email_key
FROM retail.customers.profiles;

Pitfalls

Default TRIM strips only whitespace. It will not remove quotes, brackets, or punctuation. Use BTRIM(str, chars) to trim an arbitrary character set.
Non-breaking spaces (U+00A0) and other Unicode space characters are stripped, but exotic code points such as zero-width joiners or BOM markers are not whitespace and will remain. Normalise with NORMALIZE/REPLACE if those appear in your data.
TRIM does not collapse interior whitespace. To squeeze runs of internal spaces, combine with REGEXP_REPLACE(col, '\s+', ' ', 'g').
Comparing trimmed and un-trimmed values silently fails joins. Trim both sides of any join or WHERE condition that might see user-typed data.