TRIM (Transact-SQL)
Applies to:
SQL Server 2017 (14.x) and later
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
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 and Azure SQL Database:
TRIM ( [ characters FROM ] string )
Syntax for SQL Server 2022 (16.x) and later:
Important
You will need your database compatibility level set to 160 to use the LEADING, TRAILING, or BOTH keywords.
TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )
Syntax for Azure SQL Database:
TRIM ( [ characters FROM ] string )
Syntax for Azure Synapse Analytics:
TRIM ( 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.
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
LEADINGpositional argument, the behavior is equivalent toLTRIM(@string, characters). - With optional
TRAILINGpositional 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 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 will 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 will 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