Menu

MariaDB TRIM_ORACLE(): Oracle-Compatible Trimming

Updated on

MariaDB TRIM_ORACLE() removes repeated occurrences of a trim string from the beginning, end, or both ends of an input string. It is available in all SQL modes and is a synonym for the Oracle-mode behavior of TRIM().

Syntax

TRIM_ORACLE([BOTH | LEADING | TRAILING [remstr] FROM] str)
  • str is the input string.
  • remstr is the string to remove. If omitted, spaces are removed.
  • BOTH is the default; use LEADING or TRAILING to trim only one side.

remstr is treated as a complete substring, not as a set of individual characters. For example, trimming _# removes repeated _# prefixes or suffixes, but does not remove a lone underscore left next to the text.

Trim spaces or a specified substring

SELECT QUOTE(TRIM_ORACLE('   MariaDB   ')) AS trim_both,
       QUOTE(TRIM_ORACLE(LEADING FROM '   MariaDB   ')) AS trim_leading,
       QUOTE(TRIM_ORACLE(TRAILING FROM '   MariaDB   ')) AS trim_trailing,
       TRIM_ORACLE('_#' FROM '_#_#_Hello_#_#') AS trim_substring;

trim_both | trim_leading | trim_trailing | trim_substring
----------+--------------+---------------+---------------
'MariaDB' | 'MariaDB   ' | '   MariaDB'   | _Hello

The last result retains one leading underscore because the remaining prefix is no longer a complete _# substring.

Empty results and NULL

If trimming removes the whole string, TRIM_ORACLE() returns NULL. In the default SQL mode, ordinary TRIM() returns an empty string for the same input; with SQL_MODE=Oracle, TRIM() also returns NULL:

SELECT QUOTE(TRIM('   ')) AS regular_trim,
       QUOTE(TRIM_ORACLE('   ')) AS oracle_trim;

The first result is an empty string and the second is NULL when Oracle mode is not enabled. Either function returns NULL for a NULL input.

For leading-only or trailing-only trimming, see LTRIM_ORACLE() and RTRIM_ORACLE().

Further reading