BTRIM, LTRIM and RTRIM in SQL: Trimming Spaces and Any Characters

Dirty strings are the norm: stray spaces from forms, leading zeros in codes, trailing slashes in URLs. BTRIM, LTRIM and RTRIM strip characters from the edges of a string and solve most of these with a single function.

All three share one defining property: they cut only at the edges and never touch the middle. BTRIM('a b', ' ') returns 'a b' unchanged — the spaces between the words stay, only the leading and trailing ones go. That sets them apart from REPLACE, which would delete spaces throughout the string. By default all three strip spaces, but a second argument lets you name any set of characters — zeros, slashes, hyphens, currency symbols — and that flexibility is their main edge over the bare standard TRIM.

Three functions and what they cut

In PostgreSQL the family is symmetric:

• LTRIM(s) — removes characters on the left (start);
• RTRIM(s) — removes characters on the right (end);
• BTRIM(s) — removes from both ends.

By default they strip spaces. But each function takes a second argument — the set of characters to remove.

SELECT
  LTRIM('   hello   ')        AS left_only,    -- 'hello   '
  RTRIM('   hello   ')        AS right_only,   -- '   hello'
  BTRIM('   hello   ')        AS both_sides;   -- 'hello'

A key point: the second argument is not a substring, it is a set of characters. BTRIM('xxyhelloyxx', 'xy') removes any leading and trailing character that belongs to {'x','y'}, in any order, until it hits something else.

Normalizing user data

The most common case is cleaning email and name before insert or comparison. Edge spaces break uniqueness and joins.

-- Normalize on read
SELECT id, BTRIM(LOWER(email)) AS email_clean
FROM users
WHERE BTRIM(email) <> '';

-- Fix existing rows in place
UPDATE users
SET name = BTRIM(name)
WHERE name <> BTRIM(name);

The predicate name <> BTRIM(name) updates only genuinely dirty rows — it is cheaper and keeps the write-ahead log from filling with no-op updates.

Stripping arbitrary characters: zeros and slashes

Here the character set comes into its own. Strip leading zeros from a code and a trailing slash from a path.

SELECT
  LTRIM('00042', '0')                 AS code,    -- '42'
  RTRIM('https://shop.dev/api/', '/') AS endpoint,-- 'https://shop.dev/api'
  BTRIM('--draft--', '-')             AS status;  -- 'draft'

A practical example over orders: statuses sometimes arrive with wrapper markers, and amounts come in as text with a currency symbol.

SELECT
  id,
  BTRIM(status, '*')                     AS status,
  LTRIM(CAST(amount AS text), '$') AS amount_text
FROM orders
WHERE status LIKE '%*%';

Relation to standard TRIM

BTRIM/LTRIM/RTRIM are PostgreSQL dialect names. The SQL standard defines a single TRIM operator with direction keywords:

SELECT
  TRIM(LEADING  '0' FROM '00042')  AS a,   -- same as LTRIM('00042','0')
  TRIM(TRAILING '/' FROM 'path/')  AS b,   -- same as RTRIM('path/','/')
  TRIM(BOTH     ' ' FROM '  x  ')  AS c;   -- same as BTRIM('  x  ')

The mapping is exact:

• TRIM(LEADING c FROM s) = LTRIM(s, c);
• TRIM(TRAILING c FROM s) = RTRIM(s, c);
• TRIM(BOTH c FROM s) = BTRIM(s, c).

One subtlety: in the SQL standard the trim character is a single character, whereas in BTRIM/LTRIM the second argument is a set of characters. PostgreSQL extends TRIM to accept that same character set, so TRIM(BOTH 'xy' FROM s) strips any x or y from the edges, exactly like BTRIM(s, 'xy'). When you want "strip any of these characters," the *TRIM form (or PostgreSQL's TRIM) is the natural fit — but do not assume every engine reads that argument the same way.

Differences in MySQL and ClickHouse

• MySQL: TRIM, LTRIM, RTRIM exist, but LTRIM/RTRIM take no second argument — they strip only spaces. There is no BTRIM; use TRIM(BOTH 'x' FROM s), but remember the argument here is a substring (remstr), not a character set.
• ClickHouse: it has trimLeft, trimRight, trimBoth — but those also strip only spaces. For arbitrary characters reach for trim(LEADING 'x' FROM s) or a regex such as replaceRegexpOne.

The main gotcha is confusing "character set" with "substring". RTRIM('abcxyz', 'zyx') in PostgreSQL returns 'abc' because every character in the set {z,y,x} is cut from the end, one at a time, regardless of order. TRIM(TRAILING 'zyx' FROM 'abcxyz') in MySQL returns 'abc' only because 'zyx'... no — because the literal 'xyz' matched as a whole substring at the tail. Shuffle the letters and MySQL's result changes while PostgreSQL's does not. Always confirm whether your engine treats the argument as a character set or as a substring.