MariaDB has an RTRIM() function and a RTRIM_ORACLE() function. Both functions do essentially the same thing. But there is a minor difference.

The Difference

The difference is in how each function deals with empty strings:

• RTRIM() removes trailing spaces from a string. When passed an empty string, the result will depend on whether you’re in Oracle mode or not. If in Oracle mode, it returns null. Otherwise it returns an empty string.
• RTRIM_ORACLE() is a synonym for the Oracle mode version of RTRIM(). It behaves like RTRIM() in Oracle mode (it returns null when passed an empty string), even when not in Oracle mode.

So RTRIM_ORACLE() works exactly like RTRIM() when in Oracle mode. But when not in Oracle mode, the only difference is how each function deals with empty strings.

Example

This is best explained with an example.

Default Mode

Here’s a comparison of these functions in default mode:

SET SQL_MODE=DEFAULT;
SELECT
    RTRIM(''),
    RTRIM_ORACLE('');

Result:

+-----------+------------------+
| RTRIM('') | RTRIM_ORACLE('') |
+-----------+------------------+
|           | NULL             |
+-----------+------------------+

First, I set my system to default mode (even though it was probably already in default mode), then I ran both functions with an empty string.

We can see that RTRIM() returns an empty string, while RTRIM_ORACLE() returns null.

Oracle Mode

Now let’s set it to Oracle mode and run the code again:

SET SQL_MODE=ORACLE;
SELECT
    RTRIM(''),
    RTRIM_ORACLE('');

Result:

+-----------+------------------+
| RTRIM('') | RTRIM_ORACLE('') |
+-----------+------------------+
| NULL      | NULL             |
+-----------+------------------+

We can see that RTRIM() now behaves like RTRIM_ORACLE().

So, with RTRIM(), we need to explicitly switch to Oracle mode before we get it to behave like the Oracle version of RTRIM().

RTRIM_ORACLE() on the other hand, is available in all modes, so it saves us from having to switch to Oracle mode.