How to Remove Leading & Trailing Whitespace from Column in MySQL

Often you may need to remove leading & trailing whitespaces from a column in MySQL. This is a common problem when we start analyzing data or import data from other sources, where it has not been cleaned properly. Retaining unnecessary whitespaces in your data can spoil your data analysis and reporting. For example, if the product column in sales table has values ‘Product A’ and ‘ Product A’ and you aggregate sales numbers for each product using group by clause, then these two will be displayed as separate groups even through they are the same product. Luckily there are several ways to remove leading and trailing whitespaces from strings in MySQL. In this article, we will learn how to remove leading & trailing whitespace from column in MySQL using TRIM function which is easy and versatile.

How to Remove Leading & Trailing Whitespace from Column in MySQL

Let us say you have a table students(id, full_name, age).

mysql> create table students(id int, full_name varchar(10), age int);

mysql> insert into students(id, full_name, age)
       values(1, ' John Doe', 12),
         (2, 'Jane Doe ', 15),
         (3, ' Jim Doe ', 10);

mysql> select * from students;
+------+-----------+------+
| id   | full_name | age  |
+------+-----------+------+
|    1 |  John Doe |   12 |
|    2 | Jane Doe  |   15 |
|    3 |  Jim Doe  |   10 |
+------+-----------+------+

As you can see the full_name column contains strings with leading and/or trailing spaces. You can easily do this using trim() function. Here is the syntax to use trim function.

trim(column_name or literal string)

Here is the query to remove leading and trailing spaces from full_name column.

mysql> select id, trim(full_name),age from students;
+------+-----------------+------+
| id   | trim(full_name) | age  |
+------+-----------------+------+
|    1 | John Doe        |   12 |
|    2 | Jane Doe        |   15 |
|    3 | Jim Doe         |   10 |
+------+-----------------+------+

The above query will remove leading & trailing space only while displaying the column value and not the underlying table. The original column will continue to contain these white spaces. For that, you need to use the trim() function in UPDATE statement, as shown below.

mysql> update students set full_name=trim(full_name);


mysql> select * from students;
+------+-----------+------+
| id   | full_name | age  |
+------+-----------+------+
|    1 | John Doe  |   12 |
|    2 | Jane Doe  |   15 |
|    3 | Jim Doe   |   10 |
+------+-----------+------+

You can also use trim() function on literal strings as shown below.

mysql> select trim(' hello world ');
+-----------------------+
| trim(' hello world ') |
+-----------------------+
| hello world           |
+-----------------------+

Please note, trim will remove only a single type of whitespace at a time. By default, it will remove only space character. If you want to remove different types of whitespaces, you need to nest your trim() statements as shown below. Here is an example to remove both ‘ ‘ and ‘\n’ from a column.

TRIM(BOTH ' ' FROM TRIM(BOTH '\n' FROM column))

By default, trim() function will remove both leading and trailing whitespaces. If you want to remove only leading or trailing whitespaces, you can specify them inside trim() function using leading and trailing keywords, as shown below. Here is the query to remove only trailing whitespaces.

mysql> select id, trim(trailing ' ' from full_name),age from students;
+------+-----------------------------------+------+
| id   | trim(trailing ' ' from full_name) | age  |
+------+-----------------------------------+------+
|    1 |  John Doe                         |   12 |
|    2 |   Jane Doe                        |   15 |
|    3 |  Jim Doe                          |   10 |
+------+-----------------------------------+------+

Here is the query to remove only leading whitespaces.

mysql> select id, trim(leading ' ' from full_name),age from students;
+------+----------------------------------+------+
| id   | trim(leading ' ' from full_name) | age  |
+------+----------------------------------+------+
|    1 | John Doe                         |   12 |
|    2 | Jane Doe                         |   15 |
|    3 | Jim Doe                          |   10 |
+------+----------------------------------+------+

In this article, we have learnt several ways to remove leading and trailing whitespace characters in MySQL.

Also read:

How to Remove Primary Key in MySQL
How to Find Most Frequent Column Value in MySQL
How to Truncate Foreign Key Constrained Table
How to Export MySQL Schema Without Data
How to Insert Element After Another Element in JS