SQL Reference ยท Scalar Functions
SQL TRIM(), LTRIM() and RTRIM() functions
Reference for SQL TRIM, LTRIM and RTRIM: remove spaces or other characters from the start and end of text, character sets versus strings, and dialect syntax.
TRIM(s) removes characters from both ends of a string. By default it removes spaces. LTRIM(s) strips only the left (start) side and RTRIM(s) only the right (end) side. They clean up input from forms and files, where stray spaces make equal-looking values compare as different.
Only the ends are touched. Spaces between words are never removed; for that use REPLACE().
SELECT TRIM(' hello ') AS cleaned; -- hello
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
TRIM(string) TRIM(string, characters) -- SQLite TRIM([LEADING | TRAILING | BOTH] [characters] FROM string) -- standard LTRIM(string [, characters]) RTRIM(string [, characters])
| Name | Description |
|---|---|
string |
The text to clean. |
characters |
Optional. In SQLite this is a set: every listed character is removed from the ends in any order. |
LEADING, TRAILING, BOTH |
Standard syntax for the side to trim; BOTH is the default. |
Returns: the trimmed text. NULL if the input is NULL.
Dialect differences
| Database | Behavior |
|---|---|
| SQLite | TRIM(s), TRIM(s, set), LTRIM, RTRIM. The second argument is a set of characters. Does not support the FROM syntax. |
| PostgreSQL | Standard syntax TRIM(BOTH 'x' FROM s), plus BTRIM(s, set), LTRIM(s, set), RTRIM(s, set); the characters are a set. |
| MySQL | TRIM([BOTH] 'x' FROM s) removes a repeated whole string, not a set. LTRIM and RTRIM remove only spaces. |
| SQL Server | TRIM(s) and TRIM(chars FROM s) since 2017 (a set). LTRIM / RTRIM accept characters only since 2022. |
| Oracle | `TRIM([LEADING |
-- Standard SQL, PostgreSQL, MySQL, Oracle SELECT TRIM(BOTH 'x' FROM 'xxhixx'); -- hi SELECT TRIM(LEADING '0' FROM '007'); -- 7
Usage notes
- Default is spaces only. Tabs, newlines and non-breaking spaces survive
TRIM(s). Pass them explicitly: in SQLite useCHAR(9, 10, 13, 32)as the set. - Set versus string.
TRIM('abcba', 'ab')in SQLite stripsaandbcharacters at both ends, leavingc. MySQL'sTRIM('ab' FROM s)would remove the whole stringabrepeatedly. - Remove leading zeros with
LTRIM(code, '0'), but note that'000'becomes an empty string. - Clean on insert whenever you can, and compare trimmed values only where you must;
WHERE TRIM(col) = 'x'cannot use an index oncol. - Spaces inside a value are not touched. Collapse repeated inner spaces with a loop of
REPLACE()calls or a regular expression function. - Fixed-width columns:
CHAR(n)columns in some databases pad with spaces, soTRIMorRTRIMis often needed when you read them. NULLin,NULLout. Trimming a blank string gives an empty string, which is notNULL(except in Oracle).