SQL TRIM()

SQL Server TRIM() function is used to remove both Leading and Trailing spaces from any given string

It was introduced in SQL Server 2017 version as an alternate of LRTIM() and RTRIM() function.

Syntax

TRIM (trim_string_characters from String)

• trim_string_characters is the trim characters that you want to remove from the string. By default, SQL Server removes both leading and trailing spaces from the string.
• String is the string that you want to trim.

Lets look at an example of TRIM() function In SQL.

Following statement uses TRIM() function and returns string after removing leading and trailing spaces.

As you can see, string  ‘    microsoft sql server 2017    ‘   contains three leading and three trailing spaces and the actual length of string after removing leading and trailing spaces is 25.

DECLARE @Stringtxt VARCHAR(50) ='    microsoft sql server 2017    '

SELECT @Stringtxt AS Stringtxt, DATALENGTH( @Stringtxt) AS StringLength, 
TRIM(' ' FROM @Stringtxt) AS StringAfter_Trim , 
DATALENGTH( TRIM (' ' FROM @Stringtxt )) AS StringLength_AfterTrim

As you can see the output of statement, a string length in bytes is 31 that is including leading and trailing spaces.

After removing leading and trailing spaces the length of string in bytes becomes 25 that is actual size of string in bytes.

So, TRIM() function provides the capability of removing a leading and trailing spaces in single time, earlier that is done by using two different functions which are LTRIM() and RTIM() functions .

Using TRIM function remove multiple leading and trailing characters

You can also remove a multiple leading and trailing characters from any given string.

DECLARE @Stringtxt VARCHAR(50) = 'microsoft sql server 2017' 
SELECT @Stringtxt AS Stringtxt ,
DATALENGTH( @Stringtxt) AS StringLength,
TRIM('m,i,c,r,o,s,f,t,2,0,1,7' FROM @Stringtxt) AS StringAfter_Trim , 
DATALENGTH(TRIM ('m,i,c,r,o,s,f,t,2,0,1,7' FROM @Stringtxt ) ) AS StringLength_After_Trim

In above output, TRIM() function removes the multiples leading and trailing characters from string.

Here, multiple leading characters are m,i,c,r,o,s,f,t  and trailing characters are 2,0,1,7 which are passed to TRIM() function and it removes them from string and returns ‘sql server ‘ as an output that can be seen in column StringAfter_Trim.

You can also use TRIM() function to trim the character’s set from any given string , that is more similar to the functionality of  REPLACE() function in SQL .

Using TRIM function remove leading and trailing set of characters from string

Lets remove one leading character *  and trailing set of character that is 2017 from string  ‘*microsoft sql server @2017’

DECLARE @Stringtxt VARCHAR(50) = '*microsoft sql server 2017' 
SELECT @Stringtxt AS Stringtxt ,
DATALENGTH( @Stringtxt) AS StringLength,
TRIM('*2017' FROM @Stringtxt) AS StringAfter_Trim , 
DATALENGTH(TRIM ('*2017' FROM @Stringtxt ) ) AS StringLength_After_Trim

As you can the output, it removes a leading character  ‘*’  and trailing character set ‘@2017’ from string , and the actual length in bytes is now 21.

Also Read..

LTRIM()

RTRIM()