MariaDB TRIM() Function

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

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

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

MariaDB TRIM() Syntax

Here are a few different usages of the MariaDB TRIM() function:

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

here:

• BOTH represents 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() 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 in Oracle mode, the TRIM() function will return a NULL instead of a empty string.

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

MariaDB TRIM() Examples

Remove whitespaces

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

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

Output:

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

Remove leading whitespaces

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

SELECT TRIM(LEADING FROM '   Hello   ');

Output:

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

Remove trailing whitespaces

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

SELECT TRIM(TRAILING FROM '   Hello   ');

Output:

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

Remove specified characters

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

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

Output:

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

Oracle mode

In Oracle mode, if the TRIM() will return NULL instead of a empty string.

In default mode, the following statement will return an empty string:

SELECT TRIM('   ');

Output:

+-------------+
| TRIM('   ') |
+-------------+
|             |
+-------------+

Use the following statement to switch to Oracle mode:

SET SQL_MODE=ORACLE;

And run the code again:

SELECT TRIM('   ');

Output:

+-------------+
| TRIM('   ') |
+-------------+
| NULL        |
+-------------+

In Oracle mode, TRIM() behaves exactly like the TRIM_ORACLE() function.

Conclusion

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