SQL TRIM Functions, purpose, syntax and common uses
A frequent problem arising from data entry is the unwanted white spaces accidentally added at the beginning or end of a string when the user is entering data into a textbox. These white spaces can cause issues when trying to identify duplicate records or when attempting to JOIN tables on string variables.
SQL contains two functions for trimming the white space from a string:
The LTRIM function is used to remove any white spaces from the beginning of a string.
The RTRIM function is used to remove any white spaces from the end of a string.
Both these string functions are available in SQL Server, Oracle, MySQL and Microsoft Access.
The syntax for the functions are as follows:
The String parameter that is passed to the functions can be a column name, a variable, a literal string or the output of a user defined function or scalar query.
SELECT LTRIM(' spaces at start') SELECT RTRIM(FirstName) FROM Customers
If you want to remove the white spaces at both the beginning and end of the string then you can simply apply both LTRIM and RTRIM to the string. Alternatively if you are using MySQL or Microsoft Access then you can use the TRIM function which does the same thing as applying both LTRIM and RTRIM.
-- SQL Server / Oracle UPDATE Customers SET FirstName = LTRIM(RTRIM(FirstName)) -- Access / MySQL UPDATE Customers SET FirstName = TRIM(FirstName)
In this article we have demonstrated how to remove white spaces from the beginning and end of string variables using SQL. These functions can help you clean up your data into a more standardised format and prevent issues arising from not identifying what should be duplicate records or creating successful INNER JOINS if joining on a string.