New For SQL Server 2017: T-SQL Function TRIM

There's a new T-SQL function for SQL Server 2017 named TRIM(). If you're familiar with LTRIM() and/or RTRIM(), you can probably guess what it does. Here's the definition of the TRIM() function from Microsoft Docs:

As a former and still occasional .NET developer, I've been accustomed to having the string Trim() method for years. Hell, IIRC, VB6 had a Trim() function too. So this is nothing new--but it is a welcome addition to T-SQL. Thinking back on my coding practices, it's been a common pattern for me to SELECT CHAR/NCHAR data, along with the RTRIM() function to remove trailing spaces. Since the world is full of impure data, I'd also use the LTRIM() function to remove leading spaces--just in case. (Note the return type for both LTRIM and RTRIM is NVARCHAR.) Here's an example:

SELECT LTRIM(RTRIM(b.foo))
FROM dbo.bar b;

Now I can tidy things up and remove both leading and trailing spaces with a single call to TRIM():

SELECT TRIM(b.foo)
FROM dbo.bar b;

On the surface, this may not seem like that big of a deal. And I would tend to agree. But, in this example, it saves a few key strokes and makes the code slightly more readable. And it is nice for T-SQL to finally have a function that has been around in other languages for far longer than I've been writing code for a living.

But Wait, There's More!

What about the "other specified characters" part of the function definition? That's the part that interests me. With TRIM(), you can remove more than space characters. Here's an example that removes leading and trailing commas:

SELECT TRIM( ',' FROM  '1,2,3,4,5,6,') AS [Output];

And the output:

Output
------------
1,2,3,4,5,6

(1 row affected)

You can specify more than one character in that first parameter, too. Let's remove all leading and trailing commas, sixes, and fives:

SELECT TRIM( ',65' FROM  '1,2,3,4,5,6,') AS [Output];

And the output:

Output
------------
1,2,3,4

(1 row affected)

This opens up a lot of usage opportunities and is much cleaner than some combination of other T-SQL string functions (REPLACE, LEFT, RIGHT, SUBSTRING, etc.). Unfortunately, the existing LTRIM() and RTRIM() functions haven't changed-they only affect space characters. Maybe Microsoft will enhance them in the future. Until then, happy trimming!