I noticed a post the other day by one of my friends and fellow MVP Aaron Bertrand where he mentioned that he used to scoff at the TRIM() function, at least until he read the documentation.
Almost as long as I've worked with SQL Server (since 1992), I've heard people complaining about the lack of a TRIM() function. No-one could understand why we had to keep writing both LTRIM() and RTRIM() to get the required result. So it wasn't surprising that we were all happy in SQL Server 2017 when TRIM() finally got added.
However, everyone just assumed that it was a replacement for using both those functions together, and it's not.
There are two allowed sets of syntax for using TRIM().
The first is as expected:
TRIM ( string )
and that's still the only one if you're using Azure SQL DW. However, if you're using SQL Server or Azure SQL DB, you can also use this form:
TRIM ( [ characters FROM ] string )
The first version just removes leading and trailing spaces. But the second version allows you to specify which characters will be removed. If you want spaces removed, you have to include a space in the list. You can see an example in the main image above.
What about whitespace?
Want to trim more, even more easily? In our free SDU Tools for DBAs and Developers, we have a tool called TrimWhitespace. It does way more than this built-in function, and removes all typical whitespace characters (including tabs, carriage returns, linefeeds, and all nominated Unicode whitespace characters).