Use TRIM on all records in a MySQL table?

TRIM is used to remove leading and trailing spaces. Let us first create a table −

mysql> create table DemoTable
(
   StudentName varchar(100)
);
Query OK, 0 rows affected (0.64 sec)

Insert some records in the table using insert command. Here, we have inserted records with leading and trailing whitespaces −

mysql> insert into DemoTable values(' Adam Smith ');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values(' David Miller ');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values(' Chris Brown ');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values(' Carol Taylor ');
Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-----------------------------------+
| StudentName                       |
+-----------------------------------+
| Adam Smith
| David Miller                      |
| Chris Brown                       |
| Carol Taylor                      |
+-----------------------------------+
4 rows in set (0.00 sec)

Following is the query to trim all records in a MySQL table −

mysql> update DemoTable set StudentName=trim(StudentName);
Query OK, 4 rows affected (0.24 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Let us check the table records once again −

mysql> select *from DemoTable;

This will produce the following output. All the leading and trailing whitespaces are now removed successfully using TRIM() &minuss;

+-----------------+
| StudentName     |
+-----------------+
| Adam Smith      |
| David Miller    |
| Chris Brown     |
| Carol Taylor    |
+-----------------+
4 rows in set (0.00 sec)