Modernizing Your T-SQL: Trimming Strings

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 TRIM() function.

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.

1
2
3
4

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

Compatibility

Good news, everyone! Like STRING_AGG(), 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!