Trimming Strings in SQL

1. Introduction

Data often comes in various formats, including unwanted spaces at the beginning or end of strings. Trimming strings in SQL is a fundamental operation that helps clean up this data. This makes the data easier to work with and ensures consistency in queries.

In this tutorial, we’ll explore how to trim strings in SQL using different functions across popular SQL databases like MySQL, PostgreSQL, and SQL Server.

2. Trimming Functions in SQL

We’ll explore the three most commonly used functions for trimming strings in SQL. Each of these functions serves a specific purpose:

• TRIM(): removes spaces from both ends of a string
• LTRIM(): removes spaces from the left side of a string
• RTRIM(): removes spaces from the right side of a string

Notably, SQL Server didn’t include a TRIM() function until SQL Server 2017. For SQL Server 2017 and later versions, all the examples and techniques we present in this article are applicable.

Now that we’re acquainted with these functions, let’s delve into how they’re used.

3. Trimming Strings in MySQL, PostgreSQL, and SQL Server

Before we begin exploring how these functions work, it’s important to understand that they’re compatible across major databases: MySQL, PostgreSQL, and SQL Server.

In essence, the TRIM() function is the go-to option for removing unwanted spaces surrounding string data in SQL. For illustration purposes, let’s perform trimming on a string with both trailing and leading spaces:

SELECT TRIM('         Hello World        ') AS TrimmedString;
+---------------+
| TrimmedString |
+---------------+
| Hello World   |
+---------------+
1 row in set (0.00 sec)

The output shows that the extra spaces before and after Hello World have been trimmed away.

Next, let’s use the LTRIM() function to remove leading spaces from a string in SQL. Similarly, we’ll use the same string with both trailing and leading spaces:

SELECT LTRIM('               Hello World              ') AS TrimmedString;
+---------------------------+
| TrimmedString             |
+---------------------------+
| Hello World               |
+---------------------------+
1 row in set (0.00 sec)

Notably, in the query above, we added multiple trailing and leading spaces so we can clearly see the effect of the trimming. As shown in the output, all the leading spaces before Hello World were removed, leaving only the trailing spaces.

Now, let’s use the RTRIM() function on the same string:

SELECT RTRIM('               Hello World              ') AS TrimmedString;
+----------------------------+
| TrimmedString              |
+----------------------------+
|                Hello World |
+----------------------------+
1 row in set (0.00 sec)

The RTRIM() function removes all the trailing spaces from the specified string, as illustrated in the output. Let’s take this a little bit further by applying trimming to the table column in SQL.

4. Applying Trim Functions to Table Columns in SQL

Next, we’ll explore the application of trimming functions to columns inside a table in SQL. In a real-world scenario, applying the trim functions to strings within columns in a table is more common during text manipulation.

To illustrate this, we’ll create a table with sample data that has strings with both leading and trailing spaces, then learn to apply the functions to them.

4.1. Creating Test Table and Data Samples

Let’s create a test table called Ventures that holds sample employee data (id, employee_name, and job_title). We’ll intentionally include spaces around the employee names and job titles to demonstrate the handling and cleaning up of data in SQL using the trimming functions:

CREATE TABLE Ventures (
    id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    job_title VARCHAR(50)
);

INSERT INTO Ventures (id, employee_name, job_title)
VALUES
(1, '    John Doe    ', '    Software Engineer    '),
(2, '   Jane Smith', 'Project Manager    '),
(3, ' Robert      ', '    QA Tester'),
(4, 'Alice Johnson    ', '    Data Analyst  '),
(5, '    Michael Brown', '    DevOps Engineer');

Now, we’ll view the data in the Ventures table:

SELECT * FROM Ventures;
+----+-------------------+---------------------------+
| id | employee_name     | job_title                 |
+----+-------------------+---------------------------+
|  1 |     John Doe      |     Software Engineer     |
|  2 |    Jane Smith     | Project Manager           |
|  3 |  Robert           |     QA Tester             |
|  4 | Alice Johnson     |     Data Analyst          |
|  5 |     Michael Brown |     DevOps Engineer       |
+----+-------------------+---------------------------+
5 rows in set (0.001 sec)

From the output, we can see that the names in the employee_name column and the job titles in the job_title column have the spaces we included during data insertion.

4.2. Applying Trim Functions to Columns in SQL

Here, we’ll apply the trimming functions to remove the unnecessary spaces around the strings within the employee_name and job_title columns.

Notably, we can combine the use of LTRIM() and RTRIM() to achieve the same result as the TRIM() function. So, we’ll apply all three functions to better understand them and see their results:

SELECT 
    id,
    LTRIM(RTRIM(employee_name)) AS LR_trimmedEmployeeName,
    TRIM(job_title) AS T_trimmedJobTitle
FROM Ventures;
+----+------------------------+-------------------+
| id | LR_trimmedEmployeeName | T_trimmedJobTitle |
+----+------------------------+-------------------+
|  1 | John Doe               | Software Engineer |
|  2 | Jane Smith             | Project Manager   |
|  3 | Robert                 | QA Tester         |
|  4 | Alice Johnson          | Data Analyst      |
|  5 | Michael Brown          | DevOps Engineer   |
+----+------------------------+-------------------+
5 rows in set (0.00 sec)

Applying the LTRIM() and RTRIM() functions to the employee_name column has the same effect as using the TRIM() function alone.

For context, LTRIM() removes leading spaces, while RTRIM() removes trailing spaces from the strings in the employee_name column. Similarly, the TRIM() function removes all surrounding spaces from the strings inside the job_title column.

Importantly, the TRIM() function is available in SQL Server 2017 and later versions. Therefore, in older versions of SQL Server, we won’t be able to use TRIM() directly, and we’ll need to use LTRIM() and RTRIM() instead.

5. Conclusion

In this article, we explored the LTRIM(), RTRIM(), and TRIM() functions for trimming strings in SQL. These techniques significantly improve the quality and consistency of data in SQL databases.

As demonstrated, these functions are versatile and work seamlessly across major SQL databases, such as MySQL, PostgreSQL, and SQL Server. Moreover, understanding how to apply these trimming functions to table columns is crucial in real-world scenarios where data cleanup is often necessary.