MariaDB TRIM_ORACLE() Function

In MariaDB, TRIM_ORACLE() is a built-in string function that removes the longest string consisting of specified characters from the beginning or end of a specified string.

TRIM_ORACLE() is a synonym for TRIM() function in Oracle mode.

If you just want to remove leading spaces from a string, use LTRIM() or LTRIM_ORACLE().

If you just want to remove trailing spaces from a string, use RTRIM() or RTRIM_ORACLE().

MariaDB TRIM_ORACLE() Syntax

Here are three usages of the MariaDB TRIM_ORACLE() function:

TRIM_ORACLE(str)
TRIM_ORACLE(remove_str FROM str)
TRIM_ORACLE({BOTH | LEADING | TRAILING} remove_str FROM str)

Here:

• BOTH tepresents removing leading and trailing characters
• LEADING represents removing the leading characters
• TRAILING represents removing trailing characters
• BOTH is the default value.

Parameters

str

Required. The string to process.

remove_str

Optional. The prefix and suffix strings to remove. If this is not specified remove_str, the default is blank.

Return value

The MariaDB TRIM_ORACLE() function returns a string that has the longest string consisting of the specified characters removed from the beginning or end of the specified string.

If the result is an empty string, the TRIM_ORACLE() function returns a NULL value.

If the argument is NULL, the TRIM_ORACLE() function will return NULL.

MariaDB TRIM_ORACLE() Examples

Remove whitespaces

The following statement shows two usages of using the MariaDB TRIM_ORACLE() function to remove leading and trailing whitespace from a string:

SELECT
  TRIM_ORACLE('   Hello   '),
  TRIM_ORACLE(BOTH FROM '   Hello   ');

Output:

+----------------------------+--------------------------------------+
| TRIM_ORACLE('   Hello   ') | TRIM_ORACLE(BOTH FROM '   Hello   ') |
+----------------------------+--------------------------------------+
| Hello                      | Hello                                |
+----------------------------+--------------------------------------+

Remove leading whitespaces

The following statement shows how to use the MariaDB TRIM_ORACLE() function to remove leading whitespace from strings:

SELECT TRIM_ORACLE(LEADING FROM '   Hello   ');

Output:

+-----------------------------------------+
| TRIM_ORACLE(LEADING FROM '   Hello   ') |
+-----------------------------------------+
| Hello                                   |
+-----------------------------------------+

Remove trailing whitespaces

The following statement shows how to use MariaDB TRIM_ORACLE() function to remove trailing whitespaces from a string:

SELECT TRIM_ORACLE(TRAILING FROM '   Hello   ');

Output:

+------------------------------------------+
| TRIM_ORACLE(TRAILING FROM '   Hello   ') |
+------------------------------------------+
|    Hello                                 |
+------------------------------------------+

Remove specified characters

The following statement shows how to use MariaDB TRIM_ORACLE() function to remove leading and trailing specified characters from a string:

SELECT
  TRIM_ORACLE('_' FROM '___Hello___'),
  TRIM_ORACLE(BOTH '_' FROM '___Hello___'),
  TRIM_ORACLE(LEADING '_' FROM '___Hello___'),
  TRIM_ORACLE(TRAILING '_' FROM '___Hello___'),
  TRIM_ORACLE('_#' FROM '_#_#_Hello_#_#'),
  TRIM_ORACLE(BOTH '_#' FROM '_#_#_Hello_#_#'),
  TRIM_ORACLE(LEADING '_#' FROM '_#_#_Hello_#_#'),
  TRIM_ORACLE(TRAILING '_#' FROM '_#_#_Hello_#_#')\G

Output:

*************************** 1\. row ***************************
             TRIM_ORACLE('_' FROM '___Hello___'): Hello
        TRIM_ORACLE(BOTH '_' FROM '___Hello___'): Hello
     TRIM_ORACLE(LEADING '_' FROM '___Hello___'): Hello___
    TRIM_ORACLE(TRAILING '_' FROM '___Hello___'): ___Hello
         TRIM_ORACLE('_#' FROM '_#_#_Hello_#_#'): _Hello
    TRIM_ORACLE(BOTH '_#' FROM '_#_#_Hello_#_#'): _Hello
 TRIM_ORACLE(LEADING '_#' FROM '_#_#_Hello_#_#'): _Hello_#_#
TRIM_ORACLE(TRAILING '_#' FROM '_#_#_Hello_#_#'): _#_#_Hello

Empty string

If the TRIM_ORACLE() returned string is empty then it will be returned NULL.

SELECT TRIM_ORACLE('   ');

Output:

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

This behaves exactly like the TRIM() function in Oracle mode.

Conclusion

In MariaDB, TRIM_ORACLE() is a built-in string function that removes the longest string consisting of specified characters from the beginning or end of a specified string. It is a synonym for TRIM() function in Oracle mode.