TRIM Function in SQL Server

In this SQL Server tutorial, you will understand how to manipulate the string using the TRIM function in SQL Server.

Here, using the TRIM() function, you can clean the string that contains unnecessary characters or remove spaces from the string.

First, you will learn ‘What is the TRIM() function?’ with syntax. For example, I will explain how the TRIM() function trims the characters.

After that, I will demonstrate how to remove any specified characters from the strings with an example. Then, discuss using the different keywords, such as LEADING, TRAILING, and BOTH, to remove the characters from the specific side of the strings.

Finally, you will learn how to use the TRIM() function on the table and with variables.

TRIM Function in SQL Server

TRIM function in SQL Server deletes the unwanted spaces from the beginning and end of the given string. It not only spaces but also removes any specified characters from the string.

This TRIM function in SQL Server was introduced in 2017 to help remove unnecessary characters, especially spaces (whitespace characters) from the strings.

The syntax is given below.

TRIM (characters FROM string)

Where,

  • TRIM(): The function name removes the characters from the beginning and end of the string.
  • characters: These are the characters you want to remove from the strings. It is optional, but by default, the TRIM() function removes the spaces from the strings.
  • string: This source string contains the unwanted characters or spaces you want to remove. It can be literal, column, or any string value of type varchar, nvarchar, char, and nchar.

Using the TRIM function in SQL Server, one can remove spaces, clear user input data, clean data while concatenating, etc.

Let me show you an example of how the TRIM() function removes the spaces from both sides of the string.

Suppose you have strings like ‘ HARVARD ‘, which contains spaces at the start and end. First, I want to show you the length of the string before trimming it, so use the query below.

SELECT DATALENGTH(' HARVARD ') AS StringLength; 
TRIM Function in SQL Server Checking String Length

Before timing the spaces from these strings, as you can see, the length of the string is 9; here, DATELENGTH() of SQL Server is used to know the length of the string.

Next, use the TRIM() function to remove the spaces from the string ‘ HARVARD ‘ using the query below.

SELECT TRIM(' HARVARD ') AS TrimmedSpace;
TRIM Function in SQL Server

As from the output, the spaces from the start and end of the string ‘HARVARD’ are removed. But here, you can’t see whether spaces are removed or not in the output.

One way to check it is by using the DATELENGTH() function, so pass this query part TRIM(‘ HARVARD ‘) to the DATELENGTH() function as shown below.

SELECT DATALENGTH(TRIM(' HARVARD ')) AS TrimmedSpaceLen;
TRIM Function in SQL Server Checking Trimmed String Length

After trimming the spaces, you can see the total length of the string is 7, and before trimming, the length was 9, as you have seen above. So, the TRIM() function removed two spaces, one from the beginning and the other from the end of the string.

  • Also, just so you know, we haven’t specified any characters like spaces to remove; in the above example, it is the default behaviour of the TRIM() function to remove spaces from the strings.

This is how to use the TRIM function in SQL Server to remove the unwanted spaces from the beginning and end of any string.

Next, let’s see how to remove the specified characters from both sides of the given string.

Removing Specified Characters using the TRIM function in SQL Server

So here I will explain, with examples, how you can remove any specified characters, such as @, $, &, *, !, etc, from both sides (start and end ) of the strings using the TRIM function in SQL Server.

Let’s start with a simple example, you have the string ‘##I work in the USA !’, but as you can see, this string contains the ## at the beginning and ! at the end, so to remove these characters, use the query below.

SELECT TRIM('#!' FROM '##I work in the USA !') RemovedCharacters;
Removing Specified Characters using the TRIM function in SQL Server

The specified character ‘#!’ is removed from both ends of the strings, as you can see in the above output; as a result, the ‘I work in the USA’ string is without unwanted characters.

Let’s understand the query part, TRIM(‘#!’ FROM ‘##I work in the USA !’). The first value to the TRIM() function is ‘#!’, which means these characters (any of either # or !) should be removed from the string when they appear at the beginning or end.

  • FROM is the keyword to separate the characters you want to remove and the source string containing the character.

Second, the source string ‘##I work in the USA !’ contains the characters at the beginning and end which you want to remove. Finally, after executing the query, the TRIM function removes the specified characters in the above result.

Thus, you can remove specific characters from both sides of the string using the TRIM function in SQL Server.

Next, let’s see how to remove spaces or specified characters from strings, starting or ending, which means it can begin or end from only one side.

Remove Leading and Trailing Characters using the TRIM function in SQL Server

Using the TRIM() function in the previous, you have removed the spaces or other specified characters from both sides of the strings, but you can also remove spaces or characters from only one side of the string.

But how, so the syntax of the TRIM() function you have been using till now is for the SQL Server 2022 (16.x) and earlier versions, which only allows you to remove the spaces from both sides of the string.

But Microsoft updated the TRIM() function with new keywords that allow you to remove spaces or characters from the specific side, such as start or end.

The syntax is given below, and this syntax is for the SQL Server 2022 (16.x) and later versions.

TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )

The above syntax introduces keywords you can use to control where you can remove the characters from the strings. These keywords are:

  • LEADING: When this keyword is used, the TRIM function removes only the spaces or characters from the beginning of the string.
  • TRAILING: When only this one is used, the TRIM function in SQL Server only removes the spaces or characters from the end of the string.
  • BOTH: This means removing spaces or characters from both sides of the strings, and this is the default behaviour when you don’t specify any keywords in the TRIM function.

Remove Leading Characters using the TRIM function in SQL Server

For example, you have the string ‘*The Great America*’, which contains the asterisk character at the beginning and end. So, to remove (*) characters only from the start of the string, use the query below.

SELECT TRIM(LEADING '*' FROM '*The Great America*') RemoveLeadingChar;
Remove Leading Characters using the TRIM function in SQL Server

As in the result set of the above query, the asterisk (*) character is removed from the start of the string, as you can see in the query output; as a result, the final string after trimming is ‘The Great America*’.

But here, you need to notice that it only removes the character from the beginning because the keyword LEADING is specified within the TRIM() function and the end of the character (*) is as it is.

This is how you can use the LEADING keyword with the TRIM function in SQL Server to remove spaces or characters from the start of the string.

Remove Trailing Characters using the TRIM function in SQL Server

To remove the spaces or specified characters from the end of the string, use the TRAILING keyword within the TRIM() function.

Let’s take the same example as above, but here, we will remove the asterisk (*) character from the end of the string. So, for that, use the query below.

SELECT TRIM(TRAILING '*' FROM '*The Great America*') RemoveTrailingChar;
Remove Trailing Characters using the TRIM function in SQL Server

From the output, you can see the character (*) from the end of the string is removed. That’s because the TRAILING keyword within the TRIM() function ensures that only the characters from the end of this string should be removed.

After the execution of the query, the string ‘*The Great America’ is returned as a result.

This is how to use the TRAILING keyword with the TRIM function in SQL Server to remove the characters from the end of the given string.

Next, let’s see how to use the TRIM() function with the table.

Using TRIM Function in SQL Server on Table

You have removed the spaces or characters from the string literal; here, I will show you how to use the TRIM() function on the table, and you will learn how to clean the data in your table.

For example, you have a Customer table, as shown below.

Using TRIM Function in SQL Server on Customer Table

From the table, you can see the EmailAdress column, which contains the customer’s email addresses, but most emails contain some characters such as #, %, and ^ at the end.

You have to remove those unwanted characters from the email’s end. So, you can use the TRIM function with the TRAILING keyword. For that, execute the query below.

SELECT 
         EmailAddress, 
	TRIM(TRAILING '#%^' FROM EmailAddress) AS TrimmedEmail
FROM Customer;
Using TRIM Function in SQL Server on Table

After using the TRIM() function with the TRAILING keyword, you can see in the output the specified unwanted characters are removed from each email, and now the email is clean and in a valid format.

In the query result, two columns, EmailAddress and TrimmedEmail, exist. The first contains the email with unwanted characters, and the second includes the clean email without unwanted characters.

This is how to use the TRIM function in SQL Server with a table to remove the spaces or characters.

Using Variable with TRIM Function in SQL Server

Suppose you have a variable containing the string value containing some unnecessary spaces or characters you want to remove. In that case, you can pass this variable to the TRIM() function to remove those characters.

For example, you have a variable @review containing product reviews with unwanted characters. You need to remove the unnecessary characters from it. Look at the query below.

DECLARE @review VARCHAR(100) = '!The quality of the product is good (';

SELECT TRIM(BOTH '!(' FROM @review) AS CleanedReview;
Using Variable with TRIM Function in SQL Server

As a result, the TRIM() function removed the characters at the beginning and the end of the string value in a @review variable, which you can see in the output.

Understand the query part, TRIM(BOTH ‘!(‘ FROM @review). In this query part, the first is the BOTH keywords, which means removing the specified characters from the start and end of the string.

Second is the character ‘!(‘ that needs to be removed from the string value. The third is the @review variable, which contains the string value with unwanted characters at the beginning and end.

This is how to use the variable within the TRIM function in SQL Server to remove spaces or unwanted characters from the variable.

If you want to know more about the TRIM() function, check out TRIM (Transact-SQL).

Conclusion

In this SQL Server tutorial, you learned how to use the TRIM() function to remove the whitespaces or characters from the start and end of any string.

Then, using the LEADING, TRAILING, and BOTH keywords in the TRIM() function, you remove the characters from the specific side of the string, such as from beginning, end or both.

Afterwards, you used the TRIM() function on the table column to clean the email address and then used the TRIM function to remove the specified characters from the variable.

You may like to read: