PostgreSQL TRIM Function

The TRIM() function in PostgreSQL removes specified characters (by default, spaces) from the beginning and/or end of a string. Related functions LTRIM() and RTRIM() remove characters from the left or right side only.

Syntax

-- Remove from both sides (default: spaces)
TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)

-- Alternative syntax
TRIM([BOTH] [characters FROM] string)

-- Left trim only
LTRIM(string [, characters])

-- Right trim only
RTRIM(string [, characters])

Official Documentation

Example usage

-- Remove leading and trailing spaces
SELECT TRIM('  Hello World  ') AS result;
-- Result: 'Hello World'

-- Remove only leading spaces
SELECT LTRIM('  Hello World  ') AS result;
-- Result: 'Hello World  '

-- Remove only trailing spaces
SELECT RTRIM('  Hello World  ') AS result;
-- Result: '  Hello World'

-- Remove specific characters
SELECT TRIM(BOTH '/' FROM '/path/to/file/') AS result;
-- Result: 'path/to/file'

-- Clean user input
UPDATE users
SET email = TRIM(LOWER(email))
WHERE LENGTH(TRIM(email)) != LENGTH(email);

-- Remove multiple character types
SELECT TRIM(BOTH ' .,;' FROM '  Hello, World...  ') AS result;
-- Result: 'Hello, World'

-- Using LEADING and TRAILING
SELECT TRIM(LEADING '0' FROM '000123.45') AS result;
-- Result: '123.45'

SELECT TRIM(TRAILING '0' FROM '123.45000') AS result;
-- Result: '123.45'

Common issues

1. Only affects edges: TRIM only removes characters from the beginning and end, not from the middle of the string.
2. Character set: When trimming multiple characters, any of those characters are removed, not the exact sequence.
3. NULL handling: Returns NULL if input is NULL.
4. Case sensitivity: Character matching is case-sensitive.

Best practices

1. Use TRIM to clean user input before storing in the database.
2. Apply TRIM in data validation queries to find records with unwanted whitespace.
3. Combine with other string functions like LOWER() for comprehensive data normalization.
4. Use LTRIM/RTRIM when you specifically need to preserve whitespace on one side.

Frequently Asked Questions

Q: How do I remove all whitespace including tabs and newlines?
A: Use REGEXP_REPLACE(string, '^\s+|\s+$', '', 'g') to remove all types of whitespace characters, or TRIM(E' \t\n\r' FROM string) for specific whitespace characters.

Q: Can TRIM remove characters from the middle of a string?
A: No, TRIM only removes characters from the beginning and/or end. To remove characters from anywhere in the string, use REPLACE() or REGEXP_REPLACE().

Q: What's the difference between TRIM, LTRIM, and RTRIM?
A: TRIM removes from both ends (or as specified with LEADING/TRAILING), LTRIM removes only from the left (beginning), and RTRIM removes only from the right (end).

Q: How do I trim multiple different characters?
A: List all characters in the trim specification: TRIM(BOTH ' .,;-' FROM string). PostgreSQL will remove any of these characters from both ends.

Q: Does TRIM work with multi-byte characters?
A: Yes, TRIM works correctly with multi-byte UTF-8 characters. It treats each character properly regardless of byte length.