This is one of several posts on modernizing T-SQL code with new features and functionality available in SQL Server.
Last year, you finally retired the last of your SQL Server 2008R2 instances. Congratulations! But are you taking advantage of everything that your new instances have to offer? Unless you did a review of all of the T-SQL in your applications, I’m guessing not.
This one seems pretty basic, but it’s got a trick up its sleeve – the
Every now and then, we encounter data that needs to be cleaned up because it’s got leading and/or trailing spaces. Or maybe you’re storing short data in a
CHAR(N) field, so when you query it, you’re getting trailing spaces. For time immemorial, we’ve had to wrap these fields in
rtrim(ltrim(fieldname)) to do the deed.
Effective with SQL Server 2017, that’s no longer the case. Just use
TRIM() and it’ll lop off both leading and trailing space characters, and you’re good to go. There’s no performance difference, it doesn’t behave any differently. It just makes the code cleaner to read.
Really, That’s It?
No, that’s not it. I told you that
TRIM() has a trick up its sleeve. It’s not limited to space characters. You can trim off any character as long as there’s nothing between that character and the beginning of the string. But when you do this, you lose the default space trimming. In other words, if you specify non-space characters to be trimmed, you have to also specify that spaces should be trimmed.
The syntax for this is a little unusual. It’s
TRIM('$' from YourString). That first character or collection of characters describes the character(s) you want trimmed from the string
YourString. Let’s kick the tires on it.
select trim(' hello '); -- Remove leading and trailing spaces select trim('.' from 'hello....'); -- Remove trailing periods select trim('.' from ' hello....*...');-- Remove trailing periods - note that the ones between 'o' and '*' are untouched, as are the leading spaces select trim('. *' from ' hello....*...'); -- Remove periods, asterisks, and spaces
And here are the results
Good news, everyone! Like
TRIM() only requires that the engine be SQL Server 2017 or higher. The database itself can use an older
COMPATIBILITY_LEVEL. But unlike
STRING_AGG(), the extra feature (multiple arbitrary characters) does not require a higher
COMPATIBILITY_LEVEL. As long as you’re running SQL Server 2017, you’re good to go!