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

A common use case is to remove whitespace from the start and end of the string, but we can also specify other characters to remove.

Also, as from SQL Server 2022, we can specify which side of the string to remove the characters from (i.e. leading, trailing, or both).

Syntax

The basic syntax prior to SQL Server 2022 goes like this:

TRIM ( [ characters FROM ] string )

The string argument is a required argument – it’s the actual string to trim. And characters FROM is an optional argument that allows you to specify which characters should be removed (if not the space character). If you don’t specify which characters, then the space character will be trimmed.

characters can be a literal, variable, or function call of any non-LOB character type (nvarchar, varchar, nchar, or char) containing characters that should be removed. nvarchar(max) and varchar(max) types aren’t allowed.

As from SQL Server 2022, the syntax is as follows:

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

These are defined as follows:

• LEADING removes characters specified from the start of a string.
• TRAILING removes characters specified from the end of a string.
• BOTH (default) removes characters specified from the start and end of a string.

This second syntax requires that the database compatibility level is set to 160.

Example

Here’s a basic example:

SELECT TRIM(' Cat ');

Result:

---
Cat

Here’s what it looks like without trimming the string:

SELECT ' Cat ';

Result:

-----
 Cat 

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 the effect more apparent, here’s another example that compares 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

Specify the Characters

We also have the option of specifying which characters to remove.

Example:

SELECT TRIM('GoodFood' FROM 'GoodCatFood');

Result:

Cat

Here’s an example that shows how the results can differ, depending on the characters we provide:

DECLARE @a char(7) = 'GoodCatFood';
SELECT
  TRIM('GodF'FROM @a) AS 'Godf',
  TRIM('fdog'FROM @a) AS 'fdog',
  TRIM('ofdg'FROM @a) AS 'ofdg',
  TRIM('odf'FROM @a) AS 'odf',
  TRIM('fdo'FROM @a) AS 'fdo',
  TRIM('fog'FROM @a) AS 'fog',
  TRIM('gof'FROM @a) AS 'gof';

Result:

Godf  fdog  ofdg  odf      fdo      fog   gof 
----  ----  ----  -------  -------  ----  ----
Cat   Cat   Cat   GoodCat  GoodCat  dCat  dCat

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 '----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

Specifying LEADING, TRAILING or BOTH on a version of SQL Server that’s earlier than 2022 results in a syntax error:

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

Result:

Error: Incorrect syntax near '-'.

Similar Functions

We can also use LTRIM() to remove leading characters, and RTRIM() to remove trailing characters.