MySQL Trim Whitespace Using TRIM() Function

In SQL language, the TRIM() function allows you to delete characters at the beginning and end of a string. Most often the TRIM() function is used to remove invisible characters, i.e. characters such as space, tab, line feed or even carriage return. Such a function can be useful to save space in a database or to display data properly.
 

Syntax:

The most common use is to remove the invisible characters at the beginning and end of a string using the following syntax:

SELECT TRIM('    Example    ');

The return of this function allows to get only the word “Example” without the beginning and ending spaces.
 

 
It is interesting to know that in some Database Management Systems (DBMS) such as MySQL or PostgreSQL this function can remove some specific characters.

Example 1: Delete a specific character at the beginning and end of a string:

TRIM(BOTH '-' FROM '----Example---');

Output: Example

---

Example 2: Delete a specific character at the beginning of a string:

TRIM(LEADING '-' FROM '----Example---');

Output: Example—

---

Example 3: Delete a specific character only at the end of a string:

TRIM(TRAILING '-' FROM '----Example---');

Output: —Example

---

 
Example 4: Let’s assume a table that has any type of message. These messages can contain spaces or line breaks at the beginning and end of the string.

+----------+------------+---------------------------------------+
|     id   |    date    |             message                   |
+----------+------------+---------------------------------------+
|     1    | 01-03-1993 |     819 Saint Francis Way             |
|          |            |                                       |
+----------+------------+---------------------------------------+
|     2    | 11-12-2001 |     171 Jarvisville Road Michigan     |
+----------+------------+---------------------------------------+
|          |            |                                       |
|     3    | 05-06-2004 | 188 Clay Street Indiana               |
|          |            |                                       |
+----------+------------+---------------------------------------+

It is possible to use a query to get the id, the date, and the message without the invisible characters at the beginning and at the end. The following SQL query allows to remove the spaces from the “message” column:

SELECT id, date, TRIM(message) AS message_trimed
FROM messages

Output:

+----------+------------+--------------------------------+
|     id   |    date    |        message_trimed          |
+----------+------------+--------------------------------+
|     1    | 01-03-1993 | 819 Saint Francis Way          |
+----------+------------+--------------------------------+
|     2    | 11-12-2001 | 171 Jarvisville Road Michigan  |
+----------+------------+--------------------------------+
|     3    | 05-06-2004 | 188 Clay Street Indiana        |
+----------+------------+--------------------------------+

 

 
There are 2 other very similar functions to delete characters at the beginning and end of a string.

• LTRIM(): delete characters at the beginning of a string
• RTRIM(): delete characters at the end of a string

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More