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 )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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 toLTRIM(@string, characters)
. - With optional
TRAILING
positional argument, the behavior is equivalent toRTRIM(@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 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 is 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 is 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 is the result set.
abc