TRIMFunctions
Buy Me A Coffee

Remove leading and/or trailing characters (typically whitespace) from a string.

Removes leading and/or trailing characters from a string, by default whitespace. Useful for cleaning user input, normalizing keys, and handling delimited data. Can also trim specific characters: TRIM(LEADING 0 FROM column).

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all TRIM([LEADING|TRAILING|BOTH] [remstr FROM] str). Removes whitespace by default. LTRIM() and RTRIM() for one-sided trim. Can trim specific characters: TRIM(BOTH 'x' FROM 'xxhelloxx').
PostgreSQL ✓ Supported all TRIM([LEADING|TRAILING|BOTH] [chars FROM] str). Also LTRIM(str [, chars]) and RTRIM(str [, chars]) which accept a set of characters to strip (not just whitespace). btrim() = TRIM(BOTH ...).
SQL Server ✓ Supported all LTRIM() and RTRIM() have always been available. TRIM() (both sides, whitespace) added in SQL Server 2017. TRIM() with a character set argument added in SQL Server 2022.
Oracle ✓ Supported all TRIM([LEADING|TRAILING|BOTH] [char FROM] str). LTRIM(str [, chars]) and RTRIM(str [, chars]) also available and accept character sets.
SQLite ✓ Supported all TRIM(str [, chars]), LTRIM(str [, chars]), RTRIM(str [, chars]). The optional chars argument is a set of characters to strip, not a substring.

Details

SQL Server is the notable laggard — TRIM() for both sides only arrived in 2017 (use LTRIM(RTRIM()) before that). PostgreSQL and Oracle's LTRIM/RTRIM accept a character *set* argument, not a substring — LTRIM('abcfoo', 'abc') strips any leading a, b, or c characters, not the literal substring 'abc'.

Standard Syntax

SELECT TRIM(' hello '); -- 'hello' SELECT LTRIM(' hello'); -- 'hello ' SELECT RTRIM('hello '); -- ' hello'

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

TRIM([LEADING|TRAILING|BOTH] [remstr FROM] str). Removes whitespace by default. LTRIM() and RTRIM() for one-sided trim. Can trim specific characters: TRIM(BOTH 'x' FROM 'xxhelloxx').

SELECT TRIM(' hello '); -- 'hello' SELECT TRIM(BOTH 'x' FROM 'xxhelloxx'); -- 'hello' SELECT LTRIM(' hello'); -- 'hello' SELECT RTRIM('hello '); -- 'hello'

PostgreSQL

TRIM([LEADING|TRAILING|BOTH] [chars FROM] str). Also LTRIM(str [, chars]) and RTRIM(str [, chars]) which accept a set of characters to strip (not just whitespace). btrim() = TRIM(BOTH ...).

SELECT TRIM(' hello '); -- 'hello' SELECT TRIM(BOTH 'x' FROM 'xxhelloxx'); -- 'hello' SELECT LTRIM('xxxhello', 'x'); -- 'hello' SELECT BTRIM('xxhelloxx', 'x'); -- 'hello'

SQL Server

LTRIM() and RTRIM() have always been available. TRIM() (both sides, whitespace) added in SQL Server 2017. TRIM() with a character set argument added in SQL Server 2022.

SELECT LTRIM(RTRIM(' hello ')); -- pre-2017 both-side trim SELECT TRIM(' hello '); -- 2017+ SELECT TRIM('x' FROM 'xxhelloxx'); -- 2022+ (character argument)

Oracle

TRIM([LEADING|TRAILING|BOTH] [char FROM] str). LTRIM(str [, chars]) and RTRIM(str [, chars]) also available and accept character sets.

SELECT TRIM(' hello ') FROM DUAL; -- 'hello' SELECT TRIM(BOTH 'x' FROM 'xxhelloxx') FROM DUAL; -- 'hello' SELECT LTRIM('xxxhello', 'x') FROM DUAL; -- 'hello'

SQLite

TRIM(str [, chars]), LTRIM(str [, chars]), RTRIM(str [, chars]). The optional chars argument is a set of characters to strip, not a substring.

SELECT TRIM(' hello '); -- 'hello' SELECT TRIM('xxhelloxx', 'x'); -- 'hello' SELECT LTRIM(' hello'); -- 'hello'