MariaDB has an LTRIM() function and a LTRIM_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:

• LTRIM() removes leading 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.
• LTRIM_ORACLE() is a synonym for the Oracle mode version of LTRIM(). It behaves like LTRIM() in Oracle mode (it returns null when passed an empty string), even when not in Oracle mode.

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

Result:

+-----------+------------------+
| LTRIM('') | LTRIM_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 LTRIM() returns an empty string, while LTRIM_ORACLE() returns null.

Oracle Mode

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

SET SQL_MODE=ORACLE;
SELECT
    LTRIM(''),
    LTRIM_ORACLE('');

Result:

+-----------+------------------+
| LTRIM('') | LTRIM_ORACLE('') |
+-----------+------------------+
| NULL      | NULL             |
+-----------+------------------+

We can see that LTRIM() now behaves like LTRIM_ORACLE().

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

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