SQL SERVER – 2017 – How to Remove Leading and Trailing Spaces with TRIM Function?

TRIM Function has haunted SQL DBA for ages. If you have been using SQL Server for a while, you will totally agree with me over here. In this blog post we will see how the new feature TRIM of SQL Server 2017 works in few simple words.

SQL Server DBAs and Developers have always dealt with SQL Strings and the leading and trailing spaces often makes them crazy. Well, personally, I am big believer of data quality and often leading and trailing spaces around strings are not useful.

In the earlier times developers used to two different functions LTRIM and RTRIM around the string to get necessary results. However now in SQL Server 2017 we have a new feature introduced which is TRIM(). This function works just like LTRIM and RTRIM together.

Let us first see a quick demonstration:

DECLARE @str VARCHAR(250)
SET @str =' SomeValue '
SELECT '\'+@str+'\' OriginalString, LEN (@str) OriginalLen,
'\'+TRIM(@str)+'\' TrimString, LEN(TRIM(@str)) TrimLen

When you run above script you can see that when we use function TRIM around the string it removes leading and trailing spaces. If you are using SQL Server 2017, you can use this feature. However, if you are using earlier version of SQL Server, I suggest you read my earlier blog post here which explains you how you can trim a string with the help of RTRIM and LTRIM.

Reference: Pinal Dave (https://blog.sqlauthority.com)