SQL Server RTRIM Function

In this SQL Server tutorial, you will learn about the SQL Server RTRIM function to truncate the spaces from the end of any string.

Where you will understand the syntax of the RTRIM() function with how to use it with a literal string and variable. Also how to remove trailing characters based on the specified characters.

Finally, you will learn how to use the RTRIM() function with a table column using an example.

SQL Server RTRIM Function

The RTRIM() function in SQL Server removes or truncates the spaces or any specified character from the end of the given string. This means when you need to remove trailing spaces or mentioned characters from the character expression or string, use the RTRIM() function.

The syntax is given below.

RTRIM(str_exp, specified_char)

Where,

  • RTRIM(): It is the function that removes the spaces or other specified characters from the string.
  • str_exp: It is a string expression, that can be constant, column, literal string, or variable. Provide any datatype except ntext, text and image. It accepts the value that can be implicitly converted into varchar.
  • specified_char: It is the character that you want to remove from the end of the string expression. It is optional and by default RTRIM() function removes trailing spaces from the string expression.

Additionally, if you provide the NULL value instead of string to the RTRIM() function, it returns the NULL value.

SQL Server RTRIM Function: Remove Trailing Spaces

The trailing spaces are white spaces such as spaces, tabs, or non-visible characters, which appear on the right side or end of the string.

For example, you have a literal string ‘USA ‘ with trailing spaces and want to remove the trailing spaces from that string. For that use the below query.

SELECT RTRIM('USA  ') AS RemoveTrailingSpaces;
SQL Server RTRIM Function Remove Trailing Spaces From Literal String

When you execute the above query, it truncates spaces at the end of the string ‘USA ‘ and returns a new string ‘USA’ without trailing spaces that you can see in the above output.

So this is how to remove trailing spaces from the given string using the RTRIM() function in SQL Server.

Next, let’s see how to remove the character from the end of the string based on the specified character.

SQL Server RTRIM Function: Remove Specified Character

If you want to remove a specific character other than the trailing spaces, specify that character within the RTRIM() function that must be removed from the given string.

Suppose you have the string ‘sqlserverguides.com #$$##’, this web address contains unnecessary characters such as #$$## at the end of the string. To remove these characters use the below query.

SELECT RTRIM('sqlserverguides.com #$$##','#$') AS RemoveTrailingCharacters;
SQL Server RTRIM Function Remove Specific Character

In the output of the above query, the RTRIM() function removes the characters # and $ from the end of the string ‘sqlserverguides.com #$$##’ that you can see in the above output.

As a result, it returns a new string ‘sqlserverguides.com’ without the trailing characters that you wanted to remove.

Let’s analyze the query part RTRIM(‘sqlserverguides.com #$$##’,’#$’), you have provided two things, first, is the string containing unwanted characters at the end, and the second is the specific characters (‘#$’) that you want to remove from the end of that string.

You can remove desired specified characters from the end of the string using the RTRIM() function.

SQL Server RTRIM Function: Remove Space from Variable

As you know, a variable is used to store the value for temporary purposes. So RTRIM() function can also remove trailing spaces or specified characters from the variable.

Let’s start by creating a variable named ’email’ with the value ‘sqlserverguides@gmail.com ‘; this email contains trailing spaces.

DECLARE @email VARCHAR(100) = 'sqlserverguides@gmail.com    ';

Then use the below query to remove the trailing spaces from the variable.

SELECT @email+'is email' As ActualString, 
RTRIM(@email)+' is email' AS RtrimFromVariable;

After the execution of the above query, the RTRIM() function removes the trailing spaces from the variable containing the string ‘sqlserverguides@gmail.com ‘ and returns a new string ‘sqlserverguides@gmail.com’.

  • The query part @email+’is email’ is the actual string, where it concatenates the variable @email with the literal string ‘is an email’ using the plus operator. The resultant string ‘sqlserverguides@gmail.com is email’ which contains the spaces.
  • The next query part RTRIM(@email)+’ is email’, first remove the trailing spaces from the string ‘sqlserverguides@gmail.com ‘
    • And return the new string ‘sqlserverguides@gmail.com ‘, then concatenates the literal string ‘is email’, thus the final string becomes ‘sqlserverguides@gmail.com is email’ without spaces.

This is how to remove the trailing spaces from the variable using the RTRIM() function in SQL Server.

Let’s move and see how to remove trailing spaces or specified characters from the table column.

SQL Server RTRIM Function: Remove Characters from Table

The RTRIM() function can also be used with a table column to remove the trailing spaces. For example, you have a Customer table with columns CustomerID, CustomerName, and EmailAddress which is shown below.

SQL Server RTRIM Function Remove Space from Customer Table

As you can see in the above picture, some of the email contains characters such as #,%, and ^ at the end. To remove these trailing characters use the below query.

SELECT EmailAddress, RTRIM(EmailAddress,'#%^') AS RemovedTrailingChars 
FROM Customer;
SQL Server RTRIM Function Remove Characters from Table

Now look at the above output of the query in the picture, trailing characters are removed from each email address of the customer in the EmailAddress column of the Customer table.

In the above picture look at the result which is indicated through a green arrow. Compare the email address of the customer in both columns.

Here you see the EmailAddress column which is the actual email address of the customer and the RemovedTrailingChars column which contains the email address without trailing characters.

In the query part, RTRIM(EmailAddress,’#%^’) means removing the specified character which is ‘#%^’ from the end of the email address. This is how to use the SQL Server RTRIM function on the table column.

Well, now you know how to use the RTRIM() function to remove the trailing spaces or any other character from the given string.

Conclusion

In this SQL Server tutorial, you covered how to use the RTRIM() function in SQL Server with literal strings, variables, and table columns to remove the trailing spaces or characters.

You may like to read: