Edit

TRIM (Transact-SQL)

  • Article

Applies to: SQL Server 2017 (14.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL Endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Removes the space character char(32) or other specified characters from the start and end of a string.

Starting with SQL Server 2022 (16.x), optionally remove the space character char(32) or other specified characters from the start, end, or both sides of a string.

Transact-SQL syntax conventions

Syntax

Syntax for SQL Server prior to SQL Server 2022 (16.x), Azure SQL Database, Azure Synapse Analytics, and Microsoft Fabric::

TRIM ( [ characters FROM ] string )

Syntax for SQL Server 2022 (16.x) and later, and Azure SQL Managed Instance:

Important

You will need your SQL Server database compatibility level set to 160 to use the LEADING, TRAILING, or BOTH keywords. LEADING, TRAILING, or BOTH keywords are not currently supported in Microsoft Fabric.

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

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

[ LEADING | TRAILING | BOTH ]

Applies to: SQL Server 2022 (16.x) and later, and Azure SQL Managed Instance:

The optional first argument specifies which side of the string to trim:

  • LEADING removes characters specified from the start of a string.

  • TRAILING removes characters specified from the end of a string.

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

characters

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.

string

An expression of any character type (nvarchar, varchar, nchar, or char) where characters should be removed.

Return types

Returns a character expression with a type of string argument where the space character char(32) or other specified characters are removed from both sides. Returns NULL if input string is NULL.

Remarks

By default, the TRIM function removes the space character from both the start and the end of the string. This behavior is equivalent to LTRIM(RTRIM(@string)).

To enable the optional LEADING, TRAILING, or BOTH positional arguments in SQL Server 2022 (16.x), you must enable database compatibility level 160 on the database(s) that you are connecting to when executing queries.

  • With optional LEADING positional argument, the behavior is equivalent to LTRIM(@string, characters).
  • With optional TRAILING positional argument, the behavior is equivalent to RTRIM(@string, characters).

Examples

A. Remove the space character from both sides of string

The following example removes spaces from before and after the word test.

SELECT TRIM( '     test    ') AS Result;

Here is the result set.

test

B. Remove specified characters from both sides of string

The following example provides a list of possible characters to remove from a string.

SELECT TRIM( '.,! ' FROM '     #     test    .') AS Result;

Here is the result set.

#     test

In this example, only the trailing period and spaces from before # and after the word test were removed. The other characters were ignored because they didn't exist in the string.

C. Remove specified characters from the start of a string

Important

You will need your SQL Server database compatibility level set to 160 to use the LEADING, TRAILING, or BOTH keywords. LEADING, TRAILING, or BOTH keywords are not currently supported in Microsoft Fabric.

The following example removes the leading . from the start of the string before the word test.

SELECT TRIM(LEADING '.,! ' FROM  '     .#     test    .') AS Result;

Here is the result set.

# test .

D. Remove specified characters from the end of a string

Important

You will need your SQL Server database compatibility level set to 160 to use the LEADING, TRAILING, or BOTH keywords. LEADING, TRAILING, or BOTH keywords are not currently supported in Microsoft Fabric.

The following example removes the trailing . from the end of the string after the word test.

SELECT TRIM(TRAILING '.,! ' FROM '     .#     test    .') AS Result;

Here is the result set.

.#     test

E. Remove specified characters from the beginning and end of a string

Important

You will need your SQL Server database compatibility level set to 160 to use the LEADING, TRAILING, or BOTH keywords. LEADING, TRAILING, or BOTH keywords are not currently supported in Microsoft Fabric.

The following example removes the characters 123 from the beginning and end of the string 123abc123.

SELECT TRIM(BOTH '123' FROM '123abc123') AS Result;

Here is the result set.

abc

Next steps