TRIM, LTRIM, RTRIM variations in PostgreSQL

Introduction

These functions help clean up text by removing unwanted spaces or characters from the start, end, or both sides of a string.

When you get user input with extra spaces before or after the text.

When you want to clean data imported from files that have extra spaces.

When comparing text values and want to ignore extra spaces.

When formatting output to look neat without extra spaces.

When removing specific unwanted characters from the start or end of text.

Syntax

PostgreSQL

TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)
LTRIM(string [, characters])
RTRIM(string [, characters])

TRIM removes characters from both sides by default if no LEADING or TRAILING is specified.

LTRIM removes characters only from the start (left side) of the string.

RTRIM removes characters only from the end (right side) of the string.

If characters are not specified, spaces are removed by default.

Examples

Removes spaces from both sides, result is 'hello'.

PostgreSQL

SELECT TRIM('  hello  ');

Removes spaces only from the start, result is 'hello '.

PostgreSQL

SELECT LTRIM('  hello  ');

Removes spaces only from the end, result is ' hello'.

PostgreSQL

SELECT RTRIM('  hello  ');

Removes 'x' characters from both sides, result is 'hello'.

PostgreSQL

SELECT TRIM(BOTH 'x' FROM 'xxxhelloxxx');

Sample Program

This query shows how TRIM, LTRIM, and RTRIM remove spaces or specific characters from strings.

PostgreSQL

SELECT
  TRIM('  example  ') AS trim_default,
  TRIM(LEADING ' ' FROM '  example  ') AS trim_leading,
  TRIM(TRAILING ' ' FROM '  example  ') AS trim_trailing,
  LTRIM('  example  ') AS ltrim_default,
  RTRIM('  example  ') AS rtrim_default,
  TRIM(BOTH 'x' FROM 'xxxexamplexxx') AS trim_x;

Important Notes

TRIM is more flexible because it can remove any character, not just spaces.

LTRIM and RTRIM are shortcuts for trimming only one side.

Always check if your data has spaces or other characters you want to remove.

Summary

TRIM removes characters from both sides of a string by default.

LTRIM removes characters only from the start (left side).

RTRIM removes characters only from the end (right side).