In SQL Server, you can use the TRIM() function to remove leading and trailing whitespace from a string.

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

Whitespace Example

Here’s a basic example of trimming leading and trailing whitespace from a string:

SELECT TRIM(' Cat ');

Result

---
Cat

Here’s what it would’ve looked like without trimming the string:

SELECT ' Cat ';

Result:

-----
 Cat

Note the hyphen characters are generated within my command line interface, and you can see that there’s one hyphen per character in the string (including the space character).

To make it clearer, here’s another example comparing trimmed and untrimmed strings, each with multiple whitespace characters on each side:

SELECT 
    '     Cat     ' AS 'A', 
    TRIM('     Cat     ') AS 'B';

Result:

A              B  
-------------  ---
     Cat       Cat

We can also explicitly specify the whitespace character (or any other character):

SELECT TRIM(' ' FROM ' Cat ');

Result

---
Cat

Trimming the Right or Left Side Only

From SQL Server 2022, we can specify LEADING, TRAILING, or BOTH to specify which side/s to trim the whitespace from:

SELECT 
    TRIM(LEADING ' ' FROM '     Cat     ') + 'Z' AS 'Leading',
    TRIM(TRAILING ' ' FROM '     Cat     ') + 'Z'  AS 'Trailing',
    TRIM(BOTH ' ' FROM '     Cat     ') + 'Z'  AS 'Both';

Result:

Leading        Trailing       Both          
-------------- -------------- --------------
Cat     Z           CatZ      CatZ          

This requires a database compatibility level of at least 160.

You can also use use LTRIM() to trim whitespace from the left side only, and you can use RTRIM() to trim whitespace from the right side only.

Trimming Other Characters

As mentioned, you can also use TRIM() to remove characters other than the space character.