TRIM, LTRIM, RTRIM in SQL

Introduction

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

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

When comparing text values and want to ignore spaces around them.

When preparing data for reports or display without unwanted spaces.

When importing data that may have inconsistent spacing.

When cleaning up strings before saving them to the database.

Syntax

SQL

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

TRIM removes spaces from both sides by default or can be told to remove only from the start (LEADING) or end (TRAILING).

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

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

Examples

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

SQL

SELECT TRIM('  hello  ');

Removes spaces only from the left side, result is 'hello '.

SQL

SELECT LTRIM('  hello  ');

Removes spaces only from the right side, result is ' hello'.

SQL

SELECT RTRIM('  hello  ');

Removes spaces only from the start (left), same as LTRIM.

SQL

SELECT TRIM(LEADING ' ' FROM '  hello  ');

Sample Program

This query shows how each function removes spaces differently from the string ' apple '.

SQL

SELECT
  TRIM('  apple  ') AS trimmed_both,
  LTRIM('  apple  ') AS trimmed_left,
  RTRIM('  apple  ') AS trimmed_right;

Important Notes

Spaces mean the normal space character (ASCII 32). Some databases allow trimming other characters by specifying them.

TRIM is more flexible and standard SQL, while LTRIM and RTRIM are simpler shortcuts.

Always check your database documentation as syntax can slightly differ.

Summary

Use TRIM to remove spaces from both sides of a string.

Use LTRIM to remove spaces only from the start (left side).

Use RTRIM to remove spaces only from the end (right side).