SQL Trim Whitespace: A Guide for MySQL, PostgreSQL & More

Trimming whitespace from strings is a common task when cleaning and preparing data. Different databases implement string manipulation functions differently, but they all aim to make your data cleaner and more consistent. In this guide, we will explore how to trim whitespace using SQL across various databases including MySQL, PostgreSQL, SQL Server, and SQLite.

Why Trim Whitespace?

Whitespace can sometimes accidentally be included in data, leading to issues with data consistency and accuracy in queries and reports. Removing it can help ensure that comparisons and joins work as expected.

Try It Yourself

Want to test these TRIM functions right away? You can validate your SQL queries using our SQL Syntax Checker tool, which supports MySQL, PostgreSQL, SQL Server, and SQLite dialects.

For hands-on practice with a real database, try DB Fiddle - a free online tool that lets you run SQL queries against MySQL, PostgreSQL, and SQLite databases in your browser.

Trimming Whitespace in MySQL

In MySQL, you have several functions at your disposal to trim whitespace. We’ll focus on TRIM(), which can be used to remove leading, trailing, or both leading and trailing whitespace.

Syntax

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

• BOTH: Removes both leading and trailing characters.
• LEADING: Removes characters from the front.
• TRAILING: Removes characters from the end.
• remstr: The character or whitespace to be removed (defaults to whitespace).

Example

SELECT TRIM('   Hello World   ') AS trimmed_string;

Expected Output:

+----------------+
| trimmed_string |
+----------------+
| Hello World    |
+----------------+

Example with Specific Character

SELECT TRIM(BOTH '-' FROM '---Hello World---') AS trimmed_string;

Expected Output:

+----------------+
| trimmed_string |
+----------------+
| Hello World    |
+----------------+

Trimming Whitespace in PostgreSQL

PostgreSQL provides a similar TRIM() function.

Example

SELECT TRIM('   PostgreSQL User   ') AS trimmed_string;

Expected Output:

+------------------+
| trimmed_string   |
+------------------+
| PostgreSQL User  |
+------------------+

Using Specialized Functions

PostgreSQL also supports LTRIM() and RTRIM().

• LTRIM(): Removes leading whitespace.
• RTRIM(): Removes trailing whitespace.

Examples

SELECT LTRIM('   Leading whitespace') AS ltrimmed_string;
SELECT RTRIM('Trailing whitespace   ') AS rtrimmed_string;

Expected Output:

+--------------------+
| ltrimmed_string    |
+--------------------+
| Leading whitespace |
+--------------------+

+--------------------+
| rtrimmed_string    |
+--------------------+
| Trailing whitespace|
+--------------------+

Trimming Whitespace in SQL Server

SQL Server 2017 and later versions support the standard TRIM() function, which removes both leading and trailing whitespace by default.

Using TRIM() (SQL Server 2017+)

SELECT TRIM('   SQL Server   ') AS trimmed_string;

Expected Output:

+----------------+
| trimmed_string |
+----------------+
| SQL Server     |
+----------------+

Using LTRIM() and RTRIM() (All Versions)

For older versions of SQL Server, or when you need to trim only one side, use LTRIM() and RTRIM().

SELECT LTRIM('   SQL Server') AS ltrimmed_string;
SELECT RTRIM('SQL Server   ') AS rtrimmed_string;

Expected Output:

+-----------------+
| ltrimmed_string |
+-----------------+
| SQL Server      |
+-----------------+

+-----------------+
| rtrimmed_string |
+-----------------+
| SQL Server      |
+-----------------+

To remove both leading and trailing whitespace in older versions, nest the functions:

SELECT LTRIM(RTRIM('   SQL Server   ')) AS fully_trimmed_string;

Expected Output:

+----------------------+
| fully_trimmed_string |
+----------------------+
| SQL Server           |
+----------------------+

Trimming Whitespace in SQLite

In SQLite, use the TRIM() function, which behaves similarly to MySQL’s implementation.

Example

SELECT TRIM('   Hello SQLite   ') AS trimmed_string;

Expected Output:

+---------------+
| trimmed_string|
+---------------+
| Hello SQLite  |
+---------------+

Practical Example: Cleaning Data in a Table

Here’s a real-world example showing how to use TRIM functions to clean up data in a table. Suppose you have a customers table with whitespace issues:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO customers VALUES
    (1, '  John Doe  ', '  john@example.com'),
    (2, 'Jane Smith   ', 'jane@example.com  '),
    (3, '   Bob Wilson', '   bob@example.com   ');

UPDATE customers
SET name = TRIM(name),
    email = TRIM(email);

SELECT id, CONCAT('[', name, ']') AS name, CONCAT('[', email, ']') AS email
FROM customers;

This pattern works across all major databases, though the exact syntax for CONCAT may vary slightly.

Conclusion

Trimming whitespace in SQL is a simple yet powerful operation that helps in data normalization and preparation. Each database has its own syntax and capabilities, but the core function remains consistent. Utilize these trimming functions to ensure your data is as intended, facilitating accurate queries and reports across your SQL databases.