The TRIM function of SQL Server is designed to remove leading and trailing whitespaces from a character string. A leading space is whitespace that occurs before the actual string. A trailing space occurs after.
In this article, we are going to examine the TRIM function and illustrate its usage by practical examples. So, let’s start from the basics.
TRIM Function in SQL Server
SQL TRIM is a built-in function that allows us to trim the unnecessary characters on both sides of the string with one action. Most frequently, we use it to remove whitespaces. This function appeared in SQL Server 2017, and now it is also present in Azure SQL Database.
The syntax of the SQL TRIM function is as follows:
TRIM ( [ characters FROM ] string )
- characters FROM is an optional parameter that defines which characters we should delete. By default, this parameter applies to spaces on both sides of our string.
- string is a mandatory parameter that determines the string where we need to get rid of spaces/other unnecessary characters.
The output returned is a string without those characters we determined to be trimmed at the beginning and end. See the example:
SELECT TRIM( ' example ') AS Result;The output is:
example
As we mentioned, the TRIM function can remove other characters too. Have a look at the example where we want to clean the string off unnecessary characters and spaces:
SELECT TRIM( '.,# ' FROM '# ! example .') AS Result;The output is:
! example
The SQL TRIM function is available in SQL Server starting with version 2017, but it was possible to do the task before that release too. Users could apply SQL LTRIM and SQL RTRIM functions. They are present in all supported versions of SQL Server.
LTRIM Function in SQL Server
The SQL LTRIM function serves to remove unnecessary spaces on the left side of the string. The syntax is as follows:
LTRIM( string )
string is the mandatory parameter that specifies the target string of characters we need to trim on the left side. The output is a copy of the specified string, but without the spaces at the beginning:
SELECT LTRIM(' SQL Function');Output:
‘SQL Function’
RTRIM Function in SQL Server
The SQL RTRIM function works in the same way as LTRIM – the difference is, it removes spaces on the right side of the string. The syntax is below:
RTRIM(string)
string is the required parameter that points to the string of characters where we need to remove the trailing spaces.
SELECT RTRIM('SQL Server ');Output:
‘SQL Server’
Using LTRIM and RTRIM together
Working with SQL Server, we often need to remove spaces from one side of the string only. Still, there are cases when we need to clear the string on both sides. The TRIM function we described earlier serves that goal, but, as we remember, it is only available in SQL Server 2017 and higher.
Is there a way to remove both the leading and trailing spaces for one string without the TRIM function? Yes. We can use LTRIM and RTRIM together in one query.
The syntax is:
LTRIM(RTRIM(string))
string defines that target string of characters we want to clear from unnecessary spaces on both sides. Notice also that we can put LTRIM and RTRIM in any order.
SELECT LTRIM(RTRIM(' SQL Server '));Output:
‘SQL Server’
Now that we’ve clarified the essence of all these SQL functions (TRIM, LTRIM, and RTRIM), let us dive deeper.
Why Whitespaces Matter
One might ask why it might be important to remove such spaces. In simple terms, it is because they can constitute a nuisance when, for example, comparing values. Whitespace itself is considered a part of a string if it is there, thus, it is better to care about such issues.
Let’s examine these functions properly.
First, we create a simple table for the database types run in our enterprise. Our table has three columns. The first one is the ID column required to identify each row uniquely. The second one is DBTypeNameA. The third one is the DBTypeNameB.
The last two columns differ by the data type. DBTypeNameA first uses the VARCHAR data type, and DBTypeNameB uses the CHAR data type.
For both columns, we allocate the data length of 50.
- Listing 1: Create a Simple Table
USE DB2
GO
CREATE TABLE DBType (
ID INT IDENTITY(1,1)
,DBTypeNameA VARCHAR (50)
,DBTypeNameB CHAR (50))
GO
Notice the difference between these data types.
- For the VARCHAR column, SQL Server does not allocate the space for 50 characters we expect in the column ab initio. We say that the column should make provision for a maximum of 50 characters, but allocate space as needed.
- For the CHAR column, that provision is made for 50 characters each time a row is inserted, no matter if the actual value needs that space or not.
Thus, using VARCHAR (Variable Characters) is a way of saving space.
After creating the table, we populate the same using the code in Listing 2.
-- Listing 2: Populate the Table
USE DB2
GO
INSERT INTO DBType VALUES ('SQL Server','SQL Server');
INSERT INTO DBType VALUES (' SQL Server ',' SQL Server ');
INSERT INTO DBType VALUES (' SQL Server ',' SQL Server ');
INSERT INTO DBType VALUES ('Oracle','Oracle');
INSERT INTO DBType VALUES (' Oracle ',' Oracle ');
INSERT INTO DBType VALUES (' Oracle ',' Oracle ');
INSERT INTO DBType VALUES ('MySQL','MySQL');
INSERT INTO DBType VALUES (' MySQL ',' MySQL ');
INSERT INTO DBType VALUES (' MySQL ',' MySQL ');
While populating our table, we have deliberately entered values with leading and trailing spaces. We’ll use them in our demonstration.
When we query the table (see Listing 3), we can see the “distortion” in the data as rendered in SSMS (Figure 1).
-- Listing 3: Query the Table
USE DB2
GO
SELECT * FROM DBType;
This distortion is visible because we have leading spaces. Trailing spaces are more difficult to visualize this way.
The query is Listing 4 provides a deeper look at this “distortion.” It introduces the LEN and DATALENGTH functions:
- LEN() returns the number of characters in a string excluding trailing spaces.
- DATALENGTH() returns the number of bytes used to represent an expression.
-- Listing 4: Query the Table
USE DB2
GO
SELECT DBTypeNameA
, LEN(DBTypeNameA) LenVarcharCol
, DATALENGTH(DBTypeNameA) DataLenVarcharCol
, DBTypeNameB
, LEN(DBTypeNameB) LenCharCol
, DATALENGTH(DBTypeNameB) DataLenCharCol
FROM DBType;
Figure 2 shows us variations in length for such expressions as “SQL Server,” “Oracle,” and “MySQL” due to the leading and trailing spaces.
This implies that these expressions are not the same as far as the SQL Server query engine is concerned. We can see this clearly by running the code in Listing 5.
-- Listing 5: Query for Specific
USE DB2
GO
SELECT * FROM DBType WHERE DBTypeNameA='SQL Server';
SELECT * FROM DBType WHERE DBTypeNameA='Oracle';
SELECT * FROM DBType WHERE DBTypeNameA='MySQL';
The DataLenCharCol field represents the output of the DATALENGTH() function on the CHAR column. Therefore, one consequence of this disparity between “SQL Server” and ” SQL Server ” is the query result displayed in Figure 3.
We see that even though we have three rows with each database type, our queries return only one of each because leading and trailing spaces distinguish the values.
Solving the Problem
Getting correct results for the query in Listing 5 is feasible and easy. We need the SQL Server TRIM() function as shown in Listing 6.
-- Listing 6: Query for Specific
USE DB2
GO
SELECT * FROM DBType WHERE TRIM(DBTypeNameA)='SQL Server';
SELECT * FROM DBType WHERE TRIM(DBTypeNameA)='Oracle';
SELECT * FROM DBType WHERE TRIM(DBTypeNameA)='MySQL';
Without this TRIM() function, we could get wrong results in some scenarios.
We can take this further by loading data into a separate table, assuming we wanted to solve the problem permanently (a data cleanup of sorts).
-- Listing 7: Query for Specific
USE DB2
GO
SELECT ID, TRIM(DBTypeNameA) DBTypeNameA, TRIM(DBTypeNameB) DBTypeNameB FROM DBType;
Compare the results of Listing 7 (Figure 5) with that of Listing 3 (Figure 1). We can also create another table with this result set to clean up the data (see Listing 8).
-- Listing 8: Create a New Table (Data Cleanup)
USE DB2
GO
SELECT ID, TRIM(DBTypeNameA) DBTypeNameA, TRIM(DBTypeNameB) DBTypeNameB INTO DBType_New FROM DBType;
SELECT * FROM DBType_New;
This way, we can solve our problems permanently and remove the overhead of executing functions each time we need to extract data from our table.
Conclusion
The SQL Server TRIM() functions can be used to remove both leading and trailing spaces from strings. LTRIM and RTRIM are two variants of this function that focus on leading (LEFT) and trailing (RIGHT) spaces respectively.
We can apply TRIM() on the fly to tidy up the result set and ensure getting the correct result set. Also, we can use it to remove spaces while moving the data neatly from one table to another.
Related articles
How to Parse Strings Like a Pro Using SQL SUBSTRING() Function?
Tags: sql functions, sql server, sql trim Last modified: October 31, 2021
