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. 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()

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 2,652 total views,  1 views today