SQL Server TRIM, LTRIM, and RTRIM Functions

Total: 2 Average: 5

The TRIM function of SQL Server is designed to remove leading and trailing spaces from a character string. A leading space is a space that occurs before the actual string. A trailing space occurs after.

Why Spaces 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. Space 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;
Result of Listing 3. When we query the table, we can see the “distortion” in the data as rendered in SSMS
Figure 1: Result of Listing 3

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.

Output of Listing 4 - Variations in length for such expressions as "SQL Server," "Oracle," and "MySQL" due to the leading and trailing spaces.
Figure 2: Output of Listing 4

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.

Result of Listing 5 - 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
Figure 3: Result of Listing 5

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';
Results of Listing 6 - Getting correct results for the query in Listing 5 is feasible and easy. We need the TRIM() function as shown in Listing 6
Figure 4: Results of Listing 6

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;
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)
Figure 5: Results of Listing 7

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?

Kenneth Igiri

Kenneth Igiri

Kenneth Igiri is an Enterprise Architect with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over fifteen years' experience in Information Technology with nine of those years focused on SQL Server and Oracle databases. His interests include Database Performance, HADR, Cloud Computing, Data Architecture and Enterprise Architecture. Asides from work, Kenneth teaches at Children's Church, writes faith-based fiction and helps small businesses grow. You can connect with Kenneth via his blog https://kennethigiri.com, LinkedIn, or on Amazon.com.