ערוך

שתף באמצעות

TRIM (Transact-SQL)

  • מאמר

Applies to: SQL Server 2017 (14.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics 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.

Optionally removes the space character char(32) or other specified characters from the start, end, or both sides of a string.

Starting with SQL Server 2022 (16.x), optionally removes 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 2022 (16.x) and earlier versions, Azure SQL Database, and Azure Synapse Analytics:

TRIM ( [ characters FROM ] string )

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

Important

You need your database compatibility level set to 160 to use the LEADING, TRAILING, or BOTH keywords.

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

Arguments

[ LEADING | TRAILING | BOTH ]

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

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 that you're 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's 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's 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 need your database compatibility level set to 160 to use the LEADING, TRAILING, or BOTH keywords.

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

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

Here's the result set.

# test .

D. Remove specified characters from the end of a string

Important

You need your database compatibility level set to 160 to use the LEADING, TRAILING, or BOTH keywords.

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

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

Here's the result set.

.#     test

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

Important

You need your database compatibility level set to 160 to use the LEADING, TRAILING, or BOTH keywords.

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's the result set.

abc

Related content