MySQL TRIM Function | Remove Unwanted Characters

TRIM is an inbuilt Function in MySQL which removes unwanted leading and trailing characters from a string in MySQL. Data present in tables might not always what we expect. There can be unwanted characters or spaces leading or trailing in the strings. TRIM Function is used to remove those unwanted characters. In this article, we will discuss the MySQL TRIM Function. Also, we will discuss a few examples to demonstrate it’s usage.

Syntax

TRIM([characters FROM]string)

Parameters

The MySQL TRIM Function expects only one parameter. You need to pass the string which you need to TRIM of unwanted characters. Additionally, you can also pass the characters to trim. By default, the TRIM Function removes spaces both leading and trailing the string.

Return Value

The MySQL TRIM Function returns the original string after removing unwanted characters from the string.

Examples

Let’s consider a few examples to demonstrate the usage of the function.

Example 1: Basic Trimming from Both Ends

mysql> SELECT TRIM(BOTH FROM '    Bringing    Knowledge Together   ') as finalString;
+--------------------------------+
| finalString                    |
+--------------------------------+
| Bringing    Knowledge Together |
+--------------------------------+

In the above example, you can observe the MySQL TRIM removes extra whitespaces from both ends of the string. Also, it does not remove any extra spaces between any words.

Example 2: Removing he Characters

You can also remove some selected characters from the stirng by defining them with FROM statement in the function as follows:

mysql> SELECT TRIM('#' FROM '####Bringing Knowledge Together#') as finalString;
+-----------------------------+
| finalString                 |
+-----------------------------+
| Bringing Knowledge Together |
+-----------------------------+

You can define the characters to remove before the string along with FROM keyword. The MySQL TRIM Function remove all occurrences of the defined string from the original string. However, by default the function removes spaces.

Example 3: Removing Leading Characters

mysql> SELECT TRIM(LEADING '#' FROM '####Bringing Knowledge Together#') as finalString;
+------------------------------+
| finalString                  |
+------------------------------+
| Bringing Knowledge Together# |
+------------------------------+

You can selectively remove only the leading characters from the string by using the LEADING keyword option in the query. In this way, MySQL TRIM Function only removes unwanted characters leading the original string. The characters trailing the string are left as it is.

You can also use MySQL LTRIM Function if you just need to remove only leading spaces.

mysql> SELECT LTRIM('    Bringing Knowledge Together  ') as finalString;
+-------------------------------+
| finalString                   |
+-------------------------------+
| Bringing Knowledge Together   |
+-------------------------------+

Example 4: Removing Trailing Characters from String

Similarly, you can remove only the trailing characters from the string using the TRAILING option with the TRIM Function.

mysql> SELECT TRIM(TRAILING '#' FROM '####Bringing Knowledge Together#') as finalString;
+---------------------------------+
| finalString                     |
+---------------------------------+
| ####Bringing Knowledge Together |
+---------------------------------+

Also, if you need to remove only Trailing spaces from the string, you can also use MySQL RTRIM Function.

mysql> SELECT RTRIM( '    Bringing Knowledge Together   ') as finalString;
+---------------------------------+
| finalString                     |
+---------------------------------+
|     Bringing Knowledge Together |
+---------------------------------+

Conclusion

In this article, we discussed the MySQL TRIM Function. It is used to remove unwanted characters from a string in MySQL. You can also read about other MySQL String Functions on Concatly.

Also, read more about the Function on the Official Documentation of MySQL.