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;
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.
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.