RTRIM() Function in Oracle

In Oracle, the RTRIM() function allows you to trim the right part of a string. By default, it trims white space, but you can optionally specify a different character or characters to trim.

Syntax

The syntax goes like this:

RTRIM(char [, set ])

Where char and set can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

RTRIM removes from the right end of char all of the characters contained in set. If you do not specify set, then it defaults to a single blank.

Example

Here’s an example:

SELECT RTRIM('Cat                 ')
FROM DUAL;

Result:

   RTRIM('CAT') 
_______________ 
Cat

Here, I didn’t specify which character/s to trim, and so each single blank was trimmed from the right part of the string.

It’s not easy to see the effect of the above example.

Here’s another example, with the string being concatenated with another string, while comparing the output with the original (untrimmed) string:

SELECT 'Cat     ' || 'Food'
FROM DUAL
UNION ALL
SELECT RTRIM('Cat     ') || 'Food'
FROM DUAL;

Result:

   'CAT'||'FOOD' 
________________ 
Cat     Food     
CatFood

Specify a Character

In this example I specify a character to trim. This makes it easier to see the effect:

SELECT RTRIM('...Cat...', '.')
FROM DUAL;

Result:

   RTRIM('...CAT...','.') 
_________________________ 
...Cat

So we can see that the character was trimmed from the right side but not the left side. To trim the left side, use LTRIM() or TRIM().

Note that RTRIM() only trims the character/s in the set up until there’s a character that’s not in the set. Here’s an example of what I mean:

SELECT RTRIM('Cat...B...', '.')
FROM DUAL;

Result:

   RTRIM('CAT...B...','.') 
__________________________ 
Cat...B

However, if we include B in the set, then we get a different result:

SELECT RTRIM('Cat...B...', '.B')
FROM DUAL;

Result:

   RTRIM('CAT...B...','.B') 
___________________________ 
Cat

Database Example

Here’s an example of trimming the right part of the values in a database column:

SELECT 
    country_name,
    RTRIM(country_name, 'an') AS Trimmed
FROM countries
FETCH FIRST 5 ROWS ONLY;

Result:

   COUNTRY_NAME     TRIMMED 
_______________ ___________ 
Argentina       Argenti     
Australia       Australi    
Belgium         Belgium     
Brazil          Brazil      
Canada          Canad

Null Values

If any argument is null the result is null:

SET NULL 'null';

SELECT 
    RTRIM(null, 3),
    RTRIM('Cat ', null),
    RTRIM(null, null)
FROM DUAL;

Result:

   RTRIM(NULL,3)    RTRIM('CAT',NULL)    RTRIM(NULL,NULL) 
________________ ____________________ ___________________ 
null             null                 null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Trimming Empty Strings

Passing an empty string as the second argument results in null:

SET NULL 'null';

SELECT RTRIM('Cat   ', '')
FROM DUAL;

Result:

   RTRIM('CAT','') 
__________________ 
null

But adding a single space to the empty string changes that, and trims any blank spaces from the right of the string:

SELECT RTRIM('Cat ', ' ')
FROM DUAL;

Result:

   RTRIM('CAT','') 
__________________ 
Cat

Incorrect Argument Count

Calling RTRIM() without passing any arguments returns an error:

SELECT RTRIM()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT RTRIM()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing the wrong number of arguments results in an error:

SELECT RTRIM('Cat', 1, '>')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT RTRIM('Cat', 1, '>')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: