Like many other relational databases, SQL Server provides us with a suite of string manipulations tools and functions. Amone these functions is the
trim() function that allows us to cleanup text data especially when importing them from external sources.
Before SQL Server 2017, we had to use a combination of the
RTRIM to achieve a full text trimming operation. However, this has been simplified by the introduction of the trim function.
SQL Server Trim Function
trim function in SQL Server allows us to remove a space character
char(32) or other specified characters from the start and end of a given string.
The following shows the syntax of the
trim() function in SQL Server prior to version 16.x.
TRIM([characters FROM] string)
The function parameters are expressed as below:
characters- Optional. Specifies the set of characters you want to remove. If not specified, it will remove spaces by default.
string- The source string from which you want to remove the specified characters.
If you are using SQL Server version 16.x and above, the syntax of the function is as shown:
TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )
In this case, the parameters above represent the following:
- 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.
The function returns a character rexpression with the ype of the input string where the space character or any other specified character is removed from both side.
The function will return
NULL if the input string is
By default, the
trim() function removes the space character from both the start and end of the string. This equivalent to using the combination of LTRIM and RTRIM functions as
Example 1 - Basic Usage
TRIM will remove spaces from both the beginning and end of a string. An example is as shown:
SELECT TRIM(' Hello World ') AS TrimmedString;
Example 2 - Removing Specific Characters
We can also specify which characters we wish to remove from the string as shown in the example:
SELECT TRIM('!@' FROM '!@Hello World!@!@') AS TrimmedString;
In the above example, we tell the function to remove the
@ characters from the beginning and end of the string.
Example 3 - Remove Specified Characters From End of the String
To remove a specified characters from the end of the string, we can use the
TRAILING parameter as shown in the example below:
SELECT TRIM(TRAILING '' FROM ' Hello World! ') AS Result;
Here is the resulting set:
Example 4 - Remove Specified Character From Start of the String
We can also use the
LEADING parameter to only remove the specified characters from the beginning of a given string. An example is as shown:
SELECT TRIM(LEADING ' ' FROM ' Hello World! ') AS Result;
Here is the resulting set:
Example 5 - Remove Specified Character from Both Ends
You can also use the
BOTH parameter to remove the specified set of characters from the beginning and end of the string. An example is as shown:
SELECT TRIM(BOTH '123' FROM '123Hello World123') AS Result;
In this tutorial, we learned how we can use the
trim() function in SQL Server to trim a specified set of characters from the start, end or both ends of a given string.
If you enjoyed this post, please share and leave a like down below.