The TRIM function in SQL

The SQL TRIM function is used for removing the leading and trailing spaces from the given 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. For showing the simple usage of 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:

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:

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:

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 LTRIM function for removing 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 query that used LTRIM function:

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

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. For removing leading space, use LTRIM function. For the trailing spaces, use the RTRIM function. 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.