As SQL developer, you must have often come across a scenario where you need to remove the empty spaces at the beginning and the end of the string. To achieve that, you may have used the string functions RTRIM and LTRIM. You may wish to have the TRIM function in SQL Server as it is a very popular string function in many languages.

Here, your wish comes true – The release of the SQL Server vNext provides new string function “TRIM” which you can use to get rid of the leading and trailing spaces around the string.

Till SQL Server 2016

This is how you have been trimming the leading and trailing spaces till SQL Server 2016

DECLARE @Str1 NVARCHAR(MAX)
SET @Str1 = '           Sachit           '
SELECT @Str1 OriginalString, RTRIM(LTRIM(@Str1)) TrimmedString
GO

If you try to run the trim command on SQL Server, you will below mentioned error 

Msg 195, Level 15, State 10, Line 15
‘trim’ is not a recognized built-in function name.

From SQL Server vNext onwards

Here the wish comes true with SQL Server vNext version. Now, you can use ‘TRIM’ function to remove the leading and trailing spaces.

DECLARE @Str1 NVARCHAR(MAX)
SET @Str1 = '           Sachit           '
SELECT @Str1 OriginalString, trim(@Str1) TrimmedString
GO

Happy Learning!