In SQL Server, the TRIM() function is commonly used to remove leading and trailing whitespace from a string. But did you know that you can also remove other characters from the start/end of a string? It doesn’t have to be whitespace.

TRIM() is a T-SQL function that specifically removes the space character char(32) or other specified characters from the start or end of a string.

Example

Here’s a basic example of trimming leading and trailing equals signs (=) from a string:

SELECT TRIM('=' FROM '=SPECIALS=') AS Result;

Result

Result  
--------
SPECIALS

Multiple Instances of a Character

It also trims multiple instances of the specified character.

Example:

SELECT TRIM('=' FROM '===SPECIALS===') AS Result;

Result:

Result  
--------
SPECIALS

Trimming Multiple Characters

It also allows you to trim multiple characters.

Example:

SELECT TRIM('!*=+?' FROM '=+=*!SPECIALS?=+=') AS Result;

Result:

Result  
--------
SPECIALS

Embedded White Space

If whitespace exists inside the trimmed character/s, don’t expect that whitespace to be trimmed (unless you also explicitly specify the space character as one of the characters to be trimmed).

Here’s what I mean:

SELECT TRIM('=' FROM '=   SPECIALS   =') AS Result;

Result:

Result        
--------------
   SPECIALS   

In this case, the equals sign is trimmed but the white space remains. This is because the white space is not at the immediate left and/or right of the string.

If we want to remove both the equals sign and the white space, we can simply add a space character to the list of characters to trim:

SELECT TRIM('= ' FROM '=   SPECIALS   =') AS Result;

Result:

Result  
--------
SPECIALS

Characters Surrounded by Whitespace

Similar to the previous example, you can’t expect SQL Server to trim the specified character if there’s whitespace between it and the start/end of the string.

For example:

SELECT TRIM('=' FROM '   =SPECIALS=   ') AS Result;

Result:

Result          
----------------
   =SPECIALS=

In this example, nothing was trimmed. This is because there’s whitespace between the equals signs and the start/end of the string.

In this case, you can simply add the space character as one of the characters to be trimmed:

SELECT TRIM('= ' FROM '   =SPECIALS=   ') AS Result;

Result:

Result  
--------
SPECIALS

For more examples of using TRIM() to trim whitespace from the left and right of a string, see How to Remove Leading and Trailing Whitespace in SQL Server.

Specifying Which Side to Trim

As from SQL Server 2022, we can specify which side of the string to remove the characters from. This requires that the database compatibility level is set to 160.

Here’s an example of specifying just leading characters:

SELECT TRIM(LEADING '= ' FROM '   =SPECIALS=   ');

Result:

SPECIALS=

And here’s trailing characters:

SELECT TRIM(TRAILING '= ' FROM '   =SPECIALS=   ');

Result:

   =SPECIALS

And both (the default):

SELECT TRIM(BOTH '= ' FROM '   =SPECIALS=   ');

Result:

SPECIALS