RTRIM String Function

Prior to SQL Server 2022, the RTRIM string function removes just the space character char(32) from the end of a string. However, starting from SQL Server 2022, the RTRIM string function was updated to include the removal of other specified characters from the end of a string. The characters to be removed are passed in the second argument. If no value is passed in the second argument, the space character char(32) is used as the default.

Syntax

Syntax for SQL Server prior to SQL Server 2022 (16.x):

RTRIM ( character_expression )

Syntax for SQL Server 2022 (16.x) and later, Azure SQL Managed Instance, Azure SQL Database, Azure Synapse Analytics, and Microsoft Fabric:

Important You will need your database compatibility level set to 160 to use the optional characters argument.

RTRIM ( character_expression , [ characters ] )

Arguments

character_expression

An expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type, except `text`, `ntext`, and `image`, that is implicitly convertible to `varchar`. Otherwise, use CAST to explicitly convert character_expression.

characters

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.

Return types

Returns a character expression with a type of string argument where the space character char(32) or other specified characters are removed from the end of a character_expression. Returns NULL if input string is NULL.

Sample Uses of the RTRIM String Function

Usage #1 : Determine if a String Has Trailing Spaces

DECLARE @String      VARCHAR(100)
SET @String = 'String With Trailing Spaces       '

SELECT CASE WHEN DATALENGTH(@String) = DATALENGTH(RTRIM(@String))
       THEN 'No Trailing Spaces'
       ELSE 'With Trailing Spaces' END AS [Output]

| Output               |
|----------------------|
| With Trailing Spaces |

Usage #2 : Remove or Trim Leading Spaces Without Using LTRIM

DECLARE @String      VARCHAR(100)
SET @String = '     String with Leading Spaces'

SELECT REVERSE(RTRIM(REVERSE(@String))) AS [Output]

| Output                     |
|----------------------------|
| String with Leading Spaces |

Usage #3 : Emulate Oracle's or PostgreSQL's TRIM Function

DECLARE @String      VARCHAR(100)
SET @String = '     SQL Server Helper     '
SELECT LTRIM(RTRIM(@String)) AS [Output]

| Output            |
|-------------------|
| SQL Server Helper |

Usage #4: Extract the City and State from the City, State and ZIP Code

SELECT [Building], [CityStateZIP], RTRIM([CityStateZIP], '0123456789 ') AS [CityState]
FROM (VALUES ('Chrysler Building', 'New York, NY 10174'),
                ('Empire State Building', 'New York, NY 10001'),
                ('Griffith Observatory', 'Los Angeles, CA 90027'),
                ('Independence Hall', 'Philadelphia, PA 19106'),
                ('Salesforce Tower', 'San Francisco, CA 94105'),
                ('Space Needle', 'Seattle, WA 98109'),
                ('United States Capitol', 'Washington, DC 20004'),
                ('White House', 'Washington, DC 20500'),
                ('Willis Tower', 'Chicago, IL 60606'))
AS [CityStateZIP] ([Building], [CityStateZIP])

| Building              | CityStateZIP            | CityState         |
|-----------------------|-------------------------|-------------------|
| Chrysler Building     | New York, NY 10174      | New York, NY      |
| Empire State Building | New York, NY 10001      | New York, NY      |
| Griffith Observatory  | Los Angeles, CA 90027   | Los Angeles, CA   |
| Independence Hall     | Philadelphia, PA 19106  | Philadelphia, PA  |
| Salesforce Tower      | San Francisco, CA 94105 | San Francisco, CA |
| Space Needle          | Seattle, WA 98109       | Seattle, WA       |
| United States Capitol | Washington, DC 20004    | Washington, DC    |
| White House           | Washington, DC 20500    | Washington, DC    |
| Willis Tower          | Chicago, IL 60606       | Chicago, IL       |

Related Articles:

• Sample Uses of the LTRIM Metadata Function