In this article, we will review the new SQL TRIM function in SQL Server 2017 onwards as well as providing some information on strings functions that pre-date it like LTRIM AND RTRIM.
SQL Developers usually face issueS with spaceS at the beginning and/or end of a character string. We may need to trim leading and trailing characters from a character string. We might need to do string manipulations with SQL functions as well. Suppose we want to remove spaces from the trailing end of a string, we would need to use SQL LTRIM and RTRIM functions until SQL Server 2016.
In SQL Server 2017, we get a new built-in function to trim both leading and trailing characters together with a single function. SQL TRIM function provides additional functionality of removing characters from the specified string. We can still use RTRIM and LTRIM function with SQL Server 2017 as well. Let’s explore these functions with examples.
SQL LTRIM function
It removes characters from beginning (Starting from the left side) of the specified string. In the following query, we have white space before and after the string. We need to remove space from the left side of the string using the LTRIM function.
We use the SQL DATALENGTH() function to calculate data length in bytes before and after using SQL LTRIM function.
|
1 2 3 4 5 |
DECLARE @String VARCHAR(26)= ' Application '; SELECT @String as OriginalString, LTRIM(@String) AS StringAfterTRIM, DATALENGTH(@String) AS 'DataLength String (Bytes)', DATALENGTH(LTRIM(@String)) AS 'DataLength String (Bytes) After TRIM'; |
- Data String size in Bytes for Original String: 24
- Data String size in Bytes after SQL LTRIM: 18
SQL RTRIM function
It removes characters from the end ((Starting from the right side) of the specified string. Let’s execute the following query to look at the effect of SQL RTRIM function.
|
1 2 3 4 5 |
DECLARE @String VARCHAR(26)= ' Application '; SELECT @String as OriginalString, RTRIM(@String) AS StringAfterTRIM, DATALENGTH(@String) AS 'DataLength String (Bytes)', DATALENGTH(RTRIM(@String)) AS 'DataLength String (Bytes) After TRIM'; |
- Data String size in Bytes for Original String: 24
- Data String size in Bytes after SQL RTRIM: 16
TRIM function in SQL Server 2017
Before SQL Server 2017, if we need to remove both Leading and Training spaces, we need to use both LTRIM and RTRIM function. In the following query, we used both LTRIM and RTIM functions with a string variable.
|
1 2 3 4 5 |
DECLARE @String VARCHAR(26)= ' Application '; SELECT @String as OriginalString, LTRIM(RTRIM(@String)) AS StringAfterTRIM, DATALENGTH(@String) AS 'DataLength String (Bytes)', DATALENGTH(LTRIM(RTRIM(@String))) AS 'DataLength String (Bytes) After TRIM'; |
In the following screenshot, we can see it removes both leading and trailing space from the string.
- Data String size in Bytes for Original String: 24
- Data String size in Bytes after SQL LTRIM: 11
- Data String size in Bytes for Original String: 25
- Data String size in Bytes after SQL LTRIM and RTRIM: 11
We need to use two functions to remove the spaces from the string. We can use a single instance of a TRIM function in SQL Server 2017 onwards to trim both leading and trailing characters, let’s explore the TRIM function in a further section of this article.
The Syntax of the TRIM Function
|
1 |
TRIM ([Trimcharacters FROM] string) |
- TrimCharacter: We can specify the character to remove from the string. By default, SQL Server removes spaces from both sides of a string
- String: We need to specify the string that we want to trim
Let’s explore SQL TRIM function with examples.
TRIM Leading and Training spaces:
By default, Trim function removes the space from both sides of a string. In the following query, we have to define a string with space on both sides of the string.
Execute this query:
|
1 2 3 4 5 |
DECLARE @String VARCHAR(24)= ' Application '; SELECT @String as OriginalString, TRIM(@String) AS StringAfterTRIM, DATALENGTH(@String) AS 'DataLength String (Bytes)', DATALENGTH(TRIM(@String)) AS 'DataLength String (Bytes) After TRIM'; |
We did not specify any characters to remove in TRIM function. In the output, SQL TRIM function removes both leading and trailing space.
You can also notice the difference in string length before and after the TRIM function.
- Data String size in Bytes for Original String: 24
- Data String size in Bytes after SQL TRIM: 11
TRIM Leading characters from a string:
In the previous example, we explored that SQL TRIM is the replacement of RTRIM and LTRIM and it eliminates to delete all space character before and after a string. We can remove the characters as well from the string using TRIM. Suppose we want to remove character ON from the string, we can specify it in TRIM function as per following script.
|
1 2 3 4 5 |
DECLARE @String VARCHAR(24)= 'Application'; SELECT @String as OriginalString, TRIM('On' from @String) AS StringAfterTRIM, DATALENGTH(@String) AS 'DataLength String (Bytes)', DATALENGTH( TRIM('On' from @String) ) AS 'DataLength String (Bytes) After TRIM'; |
It checks the specified characters on both the leading and trailing side and removes the particular characters. In the string Application, we have character ON in the leading side. In the following screenshot, you can look at the output after TRIM function.
- Data String size in Bytes for Original String: 11
- Data String size in Bytes after SQL TRIM: 9
Let’s look at one more example. This time we want to remove characters App from the leading side. We do not need to make any changes in the script. We can specify the character to remove using SQL TRIM, and it gives the required output.
|
1 2 3 4 5 |
DECLARE @String VARCHAR(24)= 'Application'; SELECT @String as OriginalString, TRIM('APP' from @String) AS StringAfterTRIM, DATALENGTH(@String) AS 'DataLength String (Bytes)', DATALENGTH( TRIM('APP' from @String) ) AS 'DataLength String (Bytes) After TRIM'; |
- Data String size in Bytes for Original String: 11
- Data String size in Bytes after SQL TRIM: 8
In previous examples, we removed character either from the leading end or trailing end. If we have a character on both sides, it removes them as well.
In the following example, we want to remove character A that is on both sides of the string. Execute the script, and it removes the specified characters from both sides.
|
1 2 3 4 5 |
DECLARE @String VARCHAR(24)= 'ApplicationA'; SELECT @String as OriginalString, TRIM('A' from @String) AS StringAfterTRIM, DATALENGTH(@String) AS 'DataLength String (Bytes)', DATALENGTH( TRIM('A' from @String) ) AS 'DataLength String (Bytes) After TRIM'; |
- Data String size in Bytes for Original String: 12
- Data String size in Bytes after SQL TRIM: 10
Suppose we have specified a character that is not present on the string. In the following example, we want to remove character A and C from leading or trailing end. We do not have character C on either side, so it does not remove it. It only removes character A and gives the output.
|
1 2 3 4 5 |
DECLARE @String VARCHAR(24)= 'ApplicationA'; SELECT @String as OriginalString, TRIM('AC' from @String) AS StringAfterTRIM, DATALENGTH(@String) AS 'DataLength String (Bytes)', DATALENGTH( TRIM('AC' from @String) ) AS 'DataLength String (Bytes) After TRIM'; |
- Data String size in Bytes for Original String: 12
- Data String size in Bytes after SQL TRIM: 10
TRIM for records in a table
In the previous article, we used a variable to define a string and perform SQL TRIM operation on it. Usually, we require using TRIM on table records. Let’s use it in the following example.
In the following examples, it eliminated character R from the string at the leading and trailing end.
We can use SQL TRIM function in Update statement as well. Suppose we want to update all records in Employee table and want to eliminate characters Manager from JobTitile.
|
1 2 |
UPDATE [AdventureWorks2017].[HumanResources].[Employee] SET JobTitle = TRIM ('Manager' from 'Research and Development Manager'); |
It does not treat the Manager as a single word. Trim function checks for each character in the string and eliminates it.
We can use comma (,) to separate each character as well.
|
1 2 |
UPDATE [AdventureWorks2017].[HumanResources].[Employee] SET JobTitle = TRIM('M,a,n,a,g,e,r' from 'Research and Development Manager'); |
It also does the same work and removes specific characters from the string at a leading and trailing end.
Conclusion
In this article, we explored the SQL TRIM function with SQL Server 2017 and above. We also learned about the TRIM functions LTRIM and RTRIM available on or before SQL Server 2016 as well.
Depending on the version of SQL Server you are using, you should understand these functions and be familiar with the concepts. I hope you find this article useful. Feel free to provide feedback or ask questions in the comments below.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023