TRIM, LTRIM, RTRIM in MySQL

Introduction

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

When you want to remove spaces before or after a name entered by a user.

When cleaning data imported from other sources that may have unwanted spaces.

When comparing strings and you want to ignore extra spaces.

When formatting output to look neat without extra spaces.

When preparing data for reports or exports where spaces can cause issues.

Syntax

MySQL

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

TRIM removes spaces or specified characters from both sides by default.

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'.

MySQL

SELECT TRIM('  hello  ');

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

MySQL

SELECT LTRIM('  hello  ');

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

MySQL

SELECT RTRIM('  hello  ');

Removes 'x' characters from the start only, result is 'helloxxx'.

MySQL

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

Sample Program

This query shows how each function removes spaces differently from the strings.

MySQL

SELECT
  TRIM('  apple  ') AS trimmed,
  LTRIM('  banana  ') AS left_trimmed,
  RTRIM('  cherry  ') AS right_trimmed;

Important Notes

If you do not specify LEADING, TRAILING, or BOTH in TRIM, BOTH is the default.

TRIM can remove characters other than spaces if you specify them.

Use these functions to avoid errors caused by unexpected spaces in data.

Summary

TRIM removes spaces or specified characters from both ends of a string.

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

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