MySQL has a TRIM() function that enables you to remove leading and/or trailing whitespace from a string.

You can also use it to remove other specified characters from either side (or both sides) of the string.

This article explains how to remove specified leading/trailing characters from the string.

Syntax

The TRIM() function can be used like this:

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

Or like this:

TRIM([remstr FROM] str)

What this effectively means is that you can remove whitespace, or any other specified character, from either the start of the string, the end of the string, or from both sides of the string.

So you can specify BOTH to trim characters from both ends of the string, or you could use LEADING to trim only the specify characters at the start of the string. Likewise, you could use TRAILING to remove only those characters from the end of the string.

The remstr part is where you specify the character to trim, and the str part is the actual string that needs trimming.

Example

Here’s a basic example of removing a given character from both sides of a string:

SELECT TRIM('=' FROM '=FRESH COOKIES!=') AS Trimmed;

Result:

+----------------+
| Trimmed        |
+----------------+
| FRESH COOKIES! |
+----------------+

So, the string is stripped of the specified character on both sides of the string.

Multiple Instances of the Specified Character

It will remove multiple instances of the given character (as long as it’s within a contiguous group of the same leading/trailing characters):

SELECT TRIM('=' FROM '====FRESH COOKIES!====') AS Trimmed;

Result:

+----------------+
| Trimmed        |
+----------------+
| FRESH COOKIES! |
+----------------+

Remove Leading Characters Only

You can specify that only leading characters are trimmed. To do this, use the LEADING specifier:

SELECT TRIM(LEADING '=' FROM '====FRESH COOKIES!====') AS Trimmed;

Result:

+--------------------+
| Trimmed            |
+--------------------+
| FRESH COOKIES!==== |
+--------------------+

Remove Trailing Characters Only

You can specify that only trailing characters are trimmed. To do this, use the TRAILING specifier:

SELECT TRIM(TRAILING '=' FROM '====FRESH COOKIES!====') AS Trimmed;

Result:

+--------------------+
| Trimmed            |
+--------------------+
| ====FRESH COOKIES! |
+--------------------+

Remove a Series of Characters

You can also specify that only a specific series of characters are trimmed:

SELECT TRIM(BOTH '!====' FROM '====FRESH COOKIES!====') AS Trimmed;

Result:

+-------------------+
| Trimmed           |
+-------------------+
| ====FRESH COOKIES |
+-------------------+

You might notice that I used the BOTH keyword in this example.  This is optional. The same statement could be rewritten without it:

SELECT TRIM('!====' FROM '====FRESH COOKIES!====') AS Trimmed;

Result:

+-------------------+
| Trimmed           |
+-------------------+
| ====FRESH COOKIES |
+-------------------+

Other Uses of TRIM()

You can also use TRIM() to remove trailing space, to remove leading space, or to remove both leading and trailing white space.