SQL Server Trim Function

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 LTRIM and RTRIM to achieve a full text trimming operation. However, this has been simplified by the introduction of the trim function.

SQL Server Trim Function

The 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.

Function Syntax

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.

Return Values

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 NULL

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 LTRIM(RTRIM(@string))

Examples

Example 1 - Basic Usage

By default, TRIM will remove spaces from both the beginning and end of a string. An example is as shown:

SELECT TRIM('   Hello World   ') AS TrimmedString;

Output:

Hello World

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 ! and @ 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:

 Hello World!

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:

Hello world! 

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;

Output:

Hello World

Conclusion

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.