In SQL Server, we can use the T-SQL RTRIM() function to remove trailing blanks from a given string. Trailing blanks are white spaces, tabs, etc that come at the end of the string.

Also, as from SQL Server 2022, we can specify other trailing characters to remove from the string.

Syntax

The basic syntax prior to SQL Server 2022 goes like this:

RTRIM ( character_expression )

Where character_expression is an expression of character or binary data. It can be any constant, variable, or column (as long as it’s of a data type, except text, ntext, and image, that is implicitly convertible to varchar).

As from SQL Server 2022, the syntax is as follows:

RTRIM ( character_expression , [ characters ] )

Where characters is 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.

This syntax requires that the database compatibility level is set to 160.

Example

Here’s a basic example:

SELECT RTRIM('Cat ');

Result:

---
Cat

To make the effect more apparent, here’s an example of a trimmed string and an untrimmed string side-by-side. This example also includes multiple leading whitespaces – all of which are removed.

SELECT 
    'Cat       ' AS 'A', 
    RTRIM('Cat       ') AS 'B';

Result:

A           B  
----------  ---
Cat         Cat   

Specify the Leading Characters

From SQL Server 2022, we can include a second argument that lists the trailing characters to remove. This requires that the database compatibility level is set to 160.

Example:

SELECT RTRIM('CatFood', 'Food');

Result:

Cat

Here’s an example that shows how the results can differ, depending on the characters we provide:

DECLARE @a char(7) = 'CatFood';
SELECT
  RTRIM(@a, 'Fod') AS 'Fod',
  RTRIM(@a, 'dof') AS 'dof',
  RTRIM(@a, 'odf') AS 'odf',
  RTRIM(@a, 'od') AS 'od',
  RTRIM(@a, 'do') AS 'do',
  RTRIM(@a, 'of') AS 'of',
  RTRIM(@a, 'fo') AS 'fo';

Result:

Fod     dof     odf     od      do      of      fo     
------- ------- ------- ------- ------- ------- -------
Cat     Cat     Cat     CatF    CatF    CatFood CatFood

Similar Functions

You can also use LTRIM() to remove leading characters, and TRIM() to remove spaces (or other characters) from both sides of the string.