The TRIM function in SQL

What is the TRIM function in SQL?

The SQL TRIM function is used for removing the leading and trailing spaces from the given string or column.

--Removes leading spaces
SELECT LTRIM(‘       The TRIM Example       ‘) AS Left_Trimmed;

--Removes trailing spaces
SELECT RTRIM('       The Right TRIM Example       ') AS Right_Trimmed;

--In a table column
SELECT LTRIM(emp_name) AS Right_Trimmed
FROM sto_employees;

--Remove leading and trailing spaces 
SELECT TRIM("    TRIM Function Demo    ") AS "Trimmed String";

This function is useful in many situations, however, it is particularly useful for text columns that store users’ entered information.

The users may accidentally enter leading or trailing spaces in the textboxes which information is stored in the SQL database.

While retrieving the records, you may use the TRIM, LTRIM, and RTRIM functions.

An example of LTRIM function

The LTRIM function removes the leading spaces from the specified string.

To show the simple usage of the LTRIM function, let us start with a simple string (later with column data). The LTRIM function is used and a string with leading and trailing spaces is specified.

See the query and output below:

The Query with LTRIM function:

SELECT LTRIM(‘       The TRIM Example       ‘) AS Left_Trimmed;

The result:

SQL TRIM

The example of RTRIM function

The RTRIM function is used to remove the trailing spaces from the given string. Now see the RTRIM function in action with a string with leading and trailing spaces in SQL Server:

Query:

SELECT RTRIM('       The Right TRIM Example       ') AS Right_Trimmed;

SQL RTRIM

You can see, the spaces from the right side of the string are removed while the result is still showing the gap from the left side.

The example of TRIM function in MySQL database

This example shows using the TRIM function in MySQL database. The TRIM function removes spaces from both sides of the string:

The TRIM query:

SELECT TRIM("    TRIM Function Demo    ") AS "Trimmed String";

The result:

SQL TRIM leading trailing

Using TRIM function with table column

Until now, I used the TRIM function with string constant values. Practically, you will require removing the spaces from the table columns data.

The following example shows how to use the LTRIM function to remove the leading spaces from all the values of a table column.

For that, I used the employee’s table and added a few names with leading and trailing spaces. You can see the graphic below with complete data (for demo only) and then the query that used LTRIM function:

SELECT LTRIM(emp_name) AS Right_Trimmed

FROM sto_employees;

SQL TRIM column

You see, the LTRIM function removed all the leading spaces from each record for employee names.

Variation of TRIM function in different databases

The TRIM function is used differently in various databases.

MS SQL Server

In MS SQL Server, the functions used to remove leading or trailing spaces are LTRIM and RTRIM.

The LTRIM removes spaces from the left of the string. The RTRIM removes the spaces from the right of the string.

From SQL Server 2017, the TRIM function is also available that removes the space character (char(32)) or other specified string from both sides of the string.

MySQL Database

MySQL supports all three functions.

  1. For removing leading space, use LTRIM function.
  2. For the trailing spaces, use the RTRIM function.
  3. In order to remove spaces from both sides, use the TRIM function.

Oracle

Oracle has LTRIM and RTRIM functions for removing the leading and trailing spaces, respectively.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!