Time to give the data a TRIM()

For years, when it came to getting rid of the spaces at the beginning/end of a string, you were stuck with using the functions LTRIM and RTRIM – meaning left trim and right trim respectively. For many versions of SQL Server – and probably quite often even in code written today – you will see a lot of code with the following somewhere in it:

SELECT LTRIM(RTRIM(FirstName)) AS FirstName,
	LTRIM(RTRIM(LastName)) AS LastName
FROM dbo.Person1

Starting with SQL Server 2017, you could use a new fancy function TRIM – which as would make sense – does the function of both LTRIM and RTRIM combined!

Yes, that’s right my friends! You too can bring the above query down to just the following:

SELECT TRIM(FirstName) AS FirstName,
       TRIM(LastName) AS LastName
FROM dbo.Person1

Now, a lot of developers are fairly familiar with this functionality, but TRIM can do something even better!

Let’s say you have data that’s come to you with extra characters on one side or another, and you – as a good developer – don’t want to spend the extra time in Excel or messing with PowerShell to get rid of that data, but want to do it all in SQL. For example, let’s say one row of data looks like this:

.. As funny as it is, this is great! ..”

Notice, there are two periods in front and back and some spaces there too before we get to the real data.

With the use of the TRIM function, we can get it down to “As funny as it is, this is great!” in one step – like this:

SELECT TRIM('. ' FROM '..   As funny as it is, this is great!   ..') AS Test

Yes, that’s right. Within the TRIM function itself, there is a “FROM” and you do have to specify that you want to get remove the period and the space.

Now, I know what you’re saying. But Sherpa, I have lots of data. I am definitely not typing in all of that in some weird dynamic SQL mess, then throw that into another temp table just to get it back in a usable format. No, my friends, you don’t have to.

DROP TABLE IF EXISTS #TryOutTrim; 
CREATE TABLE #TryOutTrim (
      ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
      AwfulString varchar(100) NOT NULL,
      MuchBetterString varchar(100) NULL)

INSERT INTO #TryOutTrim (AwfulString)
SELECT '..   As funny as it is, this is great!   ..' UNION ALL
SELECT '..   This is some really bad data  ...' UNION ALL
SELECT '...     It could always be worse   ......' UNION ALL
SELECT '.........        Possibly       .......................................' UNION ALL
SELECT '   ....        Definitely way worse ....   ......'

Above, we have parsed out a few rows that we can mess with into a temp table to show how we do this for real.

Originally, you’d try this code out to get rid of spaces on the left and right sides of the string.

SELECT ID, TRIM(AwfulString) FROM #TryOutTrim

But now, you can do the following to get much cleaner data!

SELECT ID, TRIM('. ' FROM AwfulString) FROM #TryOutTrim

And we get back the following:

Notice no spaces and no periods with our data!

Now we can run the following update and look at our data to validate that no spaces are showing up.

UPDATE #TryOutTrim
SET MuchBetterString = TRIM('. ' FROM AwfulString)

SELECT *, LEN(MuchBetterString)
FROM #TryOutTrim

And Boom! You’ll see only the data you expect!

Notice that the data in row 4 – “Possibly” is truly only 8 characters long. We started off with 71 characters!

Item of note:

• This will remove characters that are at the end of the strings. For example, if we expected our example string of “Possibly” to show as “Possibly.” – that’s just not going to happen with this. As long as a character is either the first or last character in the string, then it’s up for removal, if it’s in the characters to remove.
• This will not remove a character from the main string if it’s not the first or last character. For example, if we had a string ” … This is bad data. Please fix it… .. “, our example above would return “This is bad data. Please fix it”. Notice that the period is in the middle of the string, but not at the end.

And with that, I hope you have learned something new with the TRIM function. If you are using SQL Server 2017 or above, try it out today!