The release of SQL Server 2022 in November 2022 introduced a bunch of new functionality, including some enhancements to the TRIM(), LTRIM() and RTRIM() functions.

The enhancements in the LTRIM() and RTRIM() functions are different to those in the TRIM() function. Below is a quick overview of the enhancements to these functions, along with examples.

Enhancements to the TRIM() Function

Prior to SQL Server 2022 (16.x), the TRIM() function’s syntax goes like this:

TRIM ( [ characters FROM ] string )

But from SQL Server 2022 (16.x), the syntax is like this:

TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )

Basically, we can now specify which side/s to trim. Previously we could only trim both sides – we didn’t have a choice which side to trim. If we wanted to trim just one side we’d need to use LTRIM() or RTRIM().

But now we can use TRIM() to do it all.

Here’s an example of specifying just leading characters:

SELECT TRIM(LEADING '-' FROM '----Cat----');

Result:

Cat---- 

And here’s trailing characters:

SELECT TRIM(TRAILING '-' FROM '----Cat----');

Result:

----Cat 

And both (the default):

SELECT TRIM(BOTH '-' FROM '----Cat----');

Result:

Cat

It’s important to note that the database compatibility level needs to be set to 160 to use the LEADING, TRAILING, or BOTH keywords.

Enhancements to the LTRIM() and RTRIM() Functions

The changes to LTRIM() and RTRIM() are different to those in the TRIM() function.

Prior to SQL Server 2022 (16.x), the syntax for these functions goes like this:

LTRIM ( character_expression )
RTRIM ( character_expression )

So we can’t specify which character/s to trim.

But from SQL Server 2022 (16.x), the syntax is like this:

LTRIM ( character_expression , [ characters ] )
RTRIM ( character_expression , [ characters ] )

So we can now specify which character/s to trim.

This functionality was already present in the TRIM() function (prior to SQL Server 2022), but not in LTRIM() and RTRIM().

Now, we can specify which characters to trim in all three functions.

Here’s an example to demonstrate:

SELECT 
  LTRIM('GoodCatFood', 'God') AS 'LTRIM 1',
  LTRIM('GoodCatFood', 'Fod') AS 'LTRIM 2',
  RTRIM('GoodCatFood', 'God') AS 'RTRIM 1',
  RTRIM('GoodCatFood', 'Fod') AS 'RTRIM 2';

Result:

LTRIM 1     LTRIM 2     RTRIM 1     RTRIM 2    
----------- ----------- ----------- -----------
CatFood     GoodCatFood GoodCatF    GoodCat    

These enhancements requires that the database compatibility level is set to 160.

Despite all three functions now supporting the ability to specify the characters to trim, you may have noticed that the syntax for doing so differs between TRIM() and the other two functions. The TRIM() function uses characters FROM (where characters is the collection of characters to trim), and this comes before the input string. However, with LTRIM() and RTRIM(), we specify the input string first, then the characters to trim after. Also, we simply separate the two arguments with a comma, and we don’t use the FROM keyword.