How to Trim Whitespace from Strings in MySQL

Trim Whitespace from Strings in MySQL

MySQL provides several functions—TRIM(), LTRIM(), and RTRIM()—to remove unwanted spaces from strings, either from both ends or specifically from the left or right.

In MySQL, managing whitespace in used for maintaining clean and consistent data, especially when dealing with user inputs, data imports, and text processing.

---

Using TRIM(), LTRIM(), and RTRIM() in MySQL

The TRIM() function is used to remove whitespace from both sides of a string. Additionally, LTRIM() removes leading whitespace from the left side of a string, and RTRIM() removes trailing whitespace from the right side. These functions are commonly applied to clean up text data for consistent storage and retrieval.

TRIM() Syntax

TRIM([LEADING | TRAILING | BOTH] [remstr] FROM string)

The TRIM() function can remove specified characters (e.g., whitespace or any character) from either end or both ends of the string:

• LEADING removes characters from the start.
• TRAILING removes characters from the end.
• BOTH (default) removes characters from both ends.

LTRIM() Syntax

LTRIM(string)

The LTRIM() function removes whitespace from the left side of the string.

RTRIM() Syntax

RTRIM(string)

The RTRIM() function removes whitespace from the right side of the string.

---

Setting Up Example Table

For this tutorial, let’s create a contacts table with sample data to demonstrate whitespace trimming functions.

1. First, create a new database called company_db:

CREATE DATABASE company_db;

2. Use the company_db database:

USE company_db;

3. Create a contacts table with columns for contact_name and address:

CREATE TABLE contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    contact_name VARCHAR(100),
    address VARCHAR(255)
);

4. Insert sample data into the contacts table, including entries with leading, trailing, or excessive spaces:

INSERT INTO contacts (contact_name, address)
VALUES ('  Alice Johnson', ' 123 Main St '),
       ('Bob Smith ', '456 Oak St  '),
       ('  Carol Williams', '789 Pine St');

With this setup, we can use TRIM(), LTRIM(), and RTRIM() to clean up data in our examples.

---

Examples: Trimming Whitespace from Strings in MySQL

Let’s explore different ways to use these functions for trimming whitespace in MySQL.

---

1. Using TRIM() to Remove Leading and Trailing Spaces

To remove both leading and trailing spaces from the contact_name and address columns:

SELECT contact_name, TRIM(contact_name) AS trimmed_name,
       address, TRIM(address) AS trimmed_address
FROM contacts;

This query removes spaces from both ends of each contact_name and address, ensuring consistent output.

---

2. Using LTRIM() to Remove Leading Spaces Only

To remove only the leading spaces from contact_name:

SELECT contact_name, LTRIM(contact_name) AS left_trimmed_name
FROM contacts;

This query removes any leading whitespace from each contact_name while leaving trailing spaces intact.

---

3. Using RTRIM() to Remove Trailing Spaces Only

To remove only trailing spaces from the address column:

SELECT address, RTRIM(address) AS right_trimmed_address
FROM contacts;

This query removes trailing whitespace from each address, retaining any leading spaces that may be present.

---

4. Using TRIM() to Remove Specific Characters

To remove specific characters, such as leading and trailing hyphens (“-“) from an address, use TRIM() with a specified character:

SELECT address, TRIM(BOTH '-' FROM address) AS trimmed_address
FROM contacts;

This query removes hyphens from both ends of each address entry, leaving other characters intact. This can be useful when data has unnecessary punctuation.