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