In MariaDB, TRIM_ORACLE() is a synonym for the Oracle mode version of the TRIM() function.

TRIM_ORACLE() is available in all modes, so it saves you from having to switch to Oracle mode, which is what you’d need to do if you wanted the TRIM() function to behave like Oracle’s TRIM() function.

The difference is in how each function treats empty strings. Oracle returns null whereas MariaDB returns an empty string.

Syntax

The syntax goes like this:

TRIM_ORACLE([remstr FROM] str)

Or:

TRIM_ORACLE([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

Where str is the string to remove any trailing spaces from, and remstr is the string to remove.

If remstr is not provided, then TRIM_ORACLE() removes spaces.

Example

Here’s a basic example:

SELECT 
    '   Volcanic eruption   ' AS "Untrimmed",
    TRIM_ORACLE('   Volcanic eruption   ') AS "Trimmed";

Result:

+-------------------------+-------------------+
| Untrimmed               | Trimmed           |
+-------------------------+-------------------+
|    Volcanic eruption    | Volcanic eruption |
+-------------------------+-------------------+

This example uses the most basic syntax. All we did was provide the string to trim. We didn’t specify which character/s to trim, and so all spaces were trimmed from both sides of the string.

We can also see that the space within the string remains intact.

See How TRIM() Works in MariaDB for examples of using other options, such as specifying a string to trim, and which side of the string to trim. TRIM_ORACLE() works exactly the same way in such cases, with the possible exception of when the string to be trimmed is an empty string.

Empty Strings

Passing an empty string to TRIM_ORACLE() results in null being returned.

SELECT TRIM_ORACLE('');

Result:

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

This reflects the way Oracle’s TRIM() function deals with empty strings.

In MariaDB, the TRIM() function only returns null when you’re using Oracle mode. When not in Oracle mode, it returns an empty string.

Null Argument

If the argument is null, the result is null:

SELECT TRIM_ORACLE(null);

Result:

+-------------------+
| TRIM_ORACLE(null) |
+-------------------+
| NULL              |
+-------------------+

Missing Argument

Calling TRIM_ORACLE() without an argument results in an error:

SELECT TRIM_ORACLE();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1