TRIM (Transact-SQL)
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 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 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