TRIM()

Syntax

How it works

TRIM() is a string function that strips unwanted characters from the ends of a value. By default it removes whitespace, which makes it the go to tool for cleaning text that arrived with stray spaces from a form, a spreadsheet, or an imported file. The middle of the string is never touched.

In its simplest form, TRIM(str) removes spaces from both ends. The ANSI standard form lets you say exactly what to remove and from which side: TRIM(BOTH 'x' FROM str), TRIM(LEADING 'x' FROM str) and TRIM(TRAILING 'x' FROM str). When you only ever need one side, LTRIM(str) removes characters from the left and RTRIM(str) removes them from the right.

Support differs slightly by engine. PostgreSQL and MySQL implement the full TRIM(... FROM ...) syntax. SQLite offers TRIM(str, chars), LTRIM and RTRIM. SQL Server only gained the TRIM() function in SQL Server 2017; on older versions you combine LTRIM(RTRIM(str)) to strip both ends. TRIM is often paired with LENGTH() to verify how much was removed and with REPLACE() when you also need to fix characters inside the string.

Examples

Strip surrounding spaces

SELECT TRIM('   hello world   ') AS cleaned;

cleaned
-----------
hello world

Clean imported data before comparing

-- Emails were pasted from a spreadsheet with stray spaces.
-- Trim both sides so the WHERE match actually works.
SELECT id, email
FROM signups
WHERE TRIM(email) = 'sara@example.com';

id | email
---+---------------------
 7 | sara@example.com

Trim a specific character

-- Remove leading zeros from a code, and slashes from a path
SELECT
  TRIM(LEADING '0' FROM '000420')      AS code,
  TRIM(BOTH '/' FROM '/api/v1/users/') AS route;

code | route
-----+-------------
 420 | api/v1/users

LTRIM and RTRIM for one side only

SELECT
  LTRIM('   left') AS left_only,
  RTRIM('right   ') AS right_only;

left_only | right_only
----------+-----------
 left     | right

Standard ANSI TRIM with a chosen character

SELECT TRIM(TRAILING '.' FROM 'file.name...') AS name;

name
---------
file.name

Common mistakes

-- Expecting TRIM to remove the spaces between words
SELECT TRIM('a   b   c') AS cleaned;  -- still 'a   b   c'

-- TRIM only touches the ends. Use REPLACE for inner spaces.
SELECT REPLACE('a   b   c', ' ', '') AS cleaned;  -- 'abc'

-- These look equal but the stored value has a trailing space,
-- so the row is never found
SELECT * FROM users
WHERE city = 'Dubai';   -- stored as 'Dubai '

-- Trim the column before comparing untrimmed data
SELECT * FROM users
WHERE TRIM(city) = 'Dubai';

-- Fails on SQL Server 2016 and earlier: TRIM does not exist
SELECT TRIM(name) FROM customers;

-- Older SQL Server: combine LTRIM and RTRIM
SELECT LTRIM(RTRIM(name)) FROM customers;

Performance

TRIM() itself is cheap on a single value, but wrapping a column in TRIM() inside a WHERE clause makes the predicate non sargable: the database must trim every row before comparing, so any index on that column cannot be used. On large tables this turns an index seek into a full scan.

The durable fix is to clean the data once, on insert or in a batch update, rather than trimming on every read. Store the tidy value and keep the index useful. If you must query on a trimmed expression often, some engines let you build a functional index or a computed column on TRIM(col) so the work is done ahead of time.

When you only ever trim one side, prefer LTRIM or RTRIM over the full TRIM form; the intent is clearer and there is slightly less work for the engine to do.

Interview questions

SELECT TRIM(BOTH '0' FROM '000420') AS code;