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:
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:
SELECT RTRIM(' The Right TRIM Example ') AS Right_Trimmed;
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:
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;
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.
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.
