How the TRIM() Function Works in MySQL

In MySQL, the TRIM() function trims whitespace (or other specified characters) from the start and/or end of a string.

You can specify whether to trim from the left side, the right side, or both sides of the string.

Syntax

The function can be used in either of the following ways:

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

Where str is the string to trim, and remstr is an optional argument that specifies which character to trim.

Example 1 – Basic Usage

Here’s a basic example of removing whitespace from the both sides of a string:

SELECT TRIM(' Irish Wolfhound ') AS Trimmed;

Result:

+-----------------+
| Trimmed         |
+-----------------+
| Irish Wolfhound |
+-----------------+

Both the leading and trailing space is removed. Note that any space within the string remains intact.

To demonstrate the effect better, here’s what it looks like when compared to the untrimmed string:

SELECT 
  ' Irish Wolfhound ' AS Untrimmed,
  TRIM(' Irish Wolfhound ') AS Trimmed;

Result:

+-------------------+-----------------+
| Untrimmed         | Trimmed         |
+-------------------+-----------------+
|  Irish Wolfhound  | Irish Wolfhound |
+-------------------+-----------------+

If you look closely, you’ll see that the first result has extra space on both sides and the second one doesn’t. This is even more clearly demonstrated in the next example.

Multiple Spaces

If you have multiple spaces, all of them are trimmed:

SELECT 
  '    Irish Wolfhound    ' AS Untrimmed,
  TRIM('    Irish Wolfhound    ') AS Trimmed;

Result:

+-------------------------+-----------------+
| Untrimmed               | Trimmed         |
+-------------------------+-----------------+
|     Irish Wolfhound     | Irish Wolfhound |
+-------------------------+-----------------+

Other Characters

You’re not just limited to trimming whitespace. You can use this function to trim other characters. Here’s an example:

SELECT TRIM('=' FROM '====Irish Wolfhound====') AS Trimmed;

Result:

+-----------------+
| Trimmed         |
+-----------------+
| Irish Wolfhound |
+-----------------+

You can also specify multiple characters, but note that the order is important.

SELECT TRIM('=+' FROM '=+Irish Wolfhound+=') AS Trimmed;

Result:

+-------------------+
| Trimmed           |
+-------------------+
| Irish Wolfhound+= |
+-------------------+

So you can trim a whole word if you wish:

SELECT TRIM('Irish ' FROM 'Irish Wolfhound') AS Trimmed;

Result:

+-----------+
| Trimmed   |
+-----------+
| Wolfhound |
+-----------+

Trim Leading Character

You can also specify whether to trim just the leading space/characters, the trailing, or both.

Here’s an example of trimming just the leading character:

SELECT TRIM(LEADING '=' FROM '====Irish Wolfhound====') AS Trimmed;

Result:

+---------------------+
| Trimmed             |
+---------------------+
| Irish Wolfhound==== |
+---------------------+

Trim Trailing Character

Trimming just the trailing character:

SELECT TRIM(TRAILING '=' FROM '====Irish Wolfhound====') AS Trimmed;

Result:

+---------------------+
| Trimmed             |
+---------------------+
| ====Irish Wolfhound |
+---------------------+

Trim Both Leading and Trailing

As seen in the earlier examples, the TRIM() function trims both sides by default. However, you also have the option of explicitly stating this by using BOTH.

SELECT TRIM(BOTH '=' FROM '====Irish Wolfhound====') AS Trimmed;

Result:

+-----------------+
| Trimmed         |
+-----------------+
| Irish Wolfhound |
+-----------------+

Similar Functions

If you only need to trim leading space, consider using LTRIM(). If you only need to trim trailing space, consider using RTRIM().