Skip to content
Tutorials Center
My learning

Topics

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().

SQL
SELECT TRIM('   hello   ') AS cleaned;   -- hello

Try it

SQL Trimming spaces and other characters

Press Run (or Ctrl + Enter) to see the result here.

SQL Tabs and newlines are not spaces

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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 use CHAR(9, 10, 13, 32) as the set.
  • Set versus string. TRIM('abcba', 'ab') in SQLite strips a and b characters at both ends, leaving c. MySQL's TRIM('ab' FROM s) would remove the whole string ab repeatedly.
  • 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 on col.
  • 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, so TRIM or RTRIM is often needed when you read them.
  • NULL in, NULL out. Trimming a blank string gives an empty string, which is not NULL (except in Oracle).