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

By default it removes leading and trailing spaces, but you can specify which character to remove, as well as which side/s you’d like it removed from.

Syntax

The TRIM() function can be used as follows:

TRIM([remstr FROM] str)

Or:

TRIM([{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() removes spaces.

Example

Here’s a basic example:

SELECT 
    '   Caribbean Island   ' AS "Untrimmed",
    TRIM('   Caribbean Island   ') AS "Trimmed";

Result:

+------------------------+------------------+
| Untrimmed              | Trimmed          |
+------------------------+------------------+
|    Caribbean Island    | Caribbean Island |
+------------------------+------------------+

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.

The BOTH Argument

By default, TRIM() trims both sides of the string. However, you can explicitly specify BOTH if you wish:

SELECT 
    '   Caribbean Island   ' AS "Untrimmed",
    TRIM(BOTH FROM '   Caribbean Island   ') AS "Trimmed";

Result:

+------------------------+------------------+
| Untrimmed              | Trimmed          |
+------------------------+------------------+
|    Caribbean Island    | Caribbean Island |
+------------------------+------------------+

We get the same result as the previous example.

The LEADING Argument

Specifying LEADING limits the trim operation to just the start of the string:

SELECT 
    '   Caribbean Island   ' AS "Untrimmed",
    TRIM(LEADING FROM '   Caribbean Island   ') AS "Trimmed";

Result:

+------------------------+---------------------+
| Untrimmed              | Trimmed             |
+------------------------+---------------------+
|    Caribbean Island    | Caribbean Island    |
+------------------------+---------------------+

We can see that spaces still exist to the right side of the trimmed string. Only the left part was trimmed.

The TRAILING Argument

Specifying TRAILING limits the trim operation to just the start of the string:

SELECT 
    '   Caribbean Island   ' AS "Untrimmed",
    TRIM(TRAILING FROM '   Caribbean Island   ') AS "Trimmed";

Result:

+------------------------+---------------------+
| Untrimmed              | Trimmed             |
+------------------------+---------------------+
|    Caribbean Island    |    Caribbean Island |
+------------------------+---------------------+

This time, only the right part was trimmed. Spaces still exist to the left side of the trimmed string.

Specify a String to Trim

Here’s an example of specifying which character to trim:

SELECT 
    '...mountain...' AS "Untrimmed",
    TRIM('.' FROM '...mountain...') AS "Trimmed";

Result:

+----------------+----------+
| Untrimmed      | Trimmed  |
+----------------+----------+
| ...mountain... | mountain |
+----------------+----------+

It doesn’t have to be a single character. You can specify any string to trim:

SELECT 
    TRIM('.' FROM '.+.mountain.+.') AS "1",
    TRIM('.+' FROM '.+.mountain.+.') AS "2",
    TRIM('+.' FROM '.+.mountain.+.') AS "3",
    TRIM('.+.' FROM '.+.mountain.+.') AS "4";

Result:

+--------------+--------------+--------------+----------+
| 1            | 2            | 3            | 4        |
+--------------+--------------+--------------+----------+
| +.mountain.+ | .mountain.+. | .+.mountain. | mountain |
+--------------+--------------+--------------+----------+

You can even trim part of the word if you want:

SELECT TRIM('moun' FROM 'mountain');

Result:

+------------------------------+
| TRIM('moun' FROM 'mountain') |
+------------------------------+
| tain                         |
+------------------------------+

We can also use the BOTH, LEADING and TRAILING arguments when specifying the string to trim.

Example:

SELECT 
    TRIM(BOTH '.' FROM '...mountain...') AS "Both",
    TRIM(LEADING '.' FROM '...mountain...') AS "Leading",
    TRIM(TRAILING '.' FROM '...mountain...') AS "Trailaing";

Result:

+----------+-------------+-------------+
| Both     | Leading     | Trailaing   |
+----------+-------------+-------------+
| mountain | mountain... | ...mountain |
+----------+-------------+-------------+

Null Arguments

If given a null argument, the result is null:

SELECT TRIM(null);

Result:

+------------+
| TRIM(null) |
+------------+
| NULL       |
+------------+

Oracle Mode

When not running in Oracle mode, if the result is empty (i.e. it has a length of zero) the result is an empty string.

However, when running in Oracle mode, the result is null.

Here it is in default mode (i.e. not in Oracle mode):

SELECT TRIM('');

Result:

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

Now let’s switch to Oracle mode:

SET SQL_MODE=ORACLE;

And run the code again:

SELECT TRIM('');

Result:

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

There’s also an alternative way to do this. Instead of switching to Oracle mode, you can use TRIM_ORACLE() as the function name.

Let’s switch back to default mode:

SET SQL_MODE=DEFAULT;

And now run TRIM_ORACLE():

SELECT TRIM_ORACLE('');

Result:

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

Missing Argument

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

SELECT TRIM();

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