MariaDB has a TRIM() function and a TRIM_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:

• TRIM() removes characters from the start or end of 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.
• TRIM_ORACLE() is a synonym for the Oracle mode version of TRIM(). It behaves like TRIM() in Oracle mode (it returns null when passed an empty string), even when not in Oracle mode.

So TRIM_ORACLE() works exactly like TRIM() 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
    TRIM(''),
    TRIM_ORACLE('');

Result:

+----------+-----------------+
| TRIM('') | TRIM_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 TRIM() returns an empty string, while TRIM_ORACLE() returns null.

Oracle Mode

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

SET SQL_MODE=ORACLE;
SELECT
    TRIM(''),
    TRIM_ORACLE('');

Result:

+----------+-----------------+
| TRIM('') | TRIM_ORACLE('') |
+----------+-----------------+
| NULL     | NULL            |
+----------+-----------------+

We can see that TRIM() now behaves like TRIM_ORACLE().

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

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