The MySQL TRIM function is a built-in function that allows you to trim a string of characters around a string expression.
There are two different syntaxes that you can write using the function.
First, you can trim leading and trailing spaces from the expression by passing one parameter as shown below:
TRIM(expression);
The following query shows how the TRIM function works:
SELECT TRIM(" hello "), TRIM(" 567");
The result set will be as follows:
+-------------------+---------------+
| TRIM(" hello ") | TRIM(" 567") |
+-------------------+---------------+
| hello | 567 |
+-------------------+---------------+
As you can see, spaces around hello and 567 strings above are removed by the TRIM() function.
The second syntax of the TRIM() function allows you to trim specific characters from a specific side of the expression.
You can choose to trim the LEADING, TRAILING, or BOTH sides of the string expression:
TRIM( [LEADING | TRAILING | BOTH] expression FROM expression )
For example, you can remove Mr. honorific from Mr. Nathan as follows:
SELECT TRIM(LEADING "Mr. " FROM "Mr. Nathan");
The result will be as follows:
+----------------------------------------+
| TRIM(LEADING "Mr. " FROM "Mr. Nathan") |
+----------------------------------------+
| Nathan |
+----------------------------------------+
You can also remove trailing characters as follows:
mysql> SELECT TRIM(TRAILING " B.Sc." FROM "Nathan B.Sc.");
+---------------------------------------------+
| TRIM(TRAILING " B.Sc." FROM "Nathan B.Sc.") |
+---------------------------------------------+
| Nathan |
+---------------------------------------------+
When the string you want to remove isn’t found, MySQL simply returns the string you passed into the function.
And that’s how the TRIM() function works in MySQL. You can use it to remove certain string characters from an expression or a table column.