Remove Leading and Trailing Whitespace in a MySQL Field

To remove leading and trailing space, we can use the trim() in MySQL. Firstly, we will create a table with the help of CREATE command.

Creating a table −

mysql> CREATE table TrailingANdLeadingDemo
-> (
-> SpaceTrailLead varchar(100)
-> );
Query OK, 0 rows affected (0.57 sec)

After creating a table, we will insert a record with the help of INSERT command. Let us insert a record with leading and trailing space −

mysql> INSERT into TrailingANdLeadingDemo values(' john ');
Query OK, 1 row affected (0.12 sec)

We can display all the records with the help of SELECT command

Displaying records

mysql> SELECT * from TrailingANdLeadingDemo;

The following is the output

+----------------+
| SpaceTrailLead |
+----------------+
| john           |
+----------------+
1 row in set (0.00 sec)

Now, record has been inserted with some space. The syntax to remove leading and trailing space with the help of trim() function is as follows −

UPDATE yourTableName set column_name=trim(column_name);

Now, I am applying the above query to remove trailing and leading space −

mysql> UPDATE TrailingANdLeadingDemo set SpaceTrailLead=trim(SpaceTrailLead);
Query OK, 1 row affected (0.17 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now, we can check whether the space is removed or not. Use SELECT statement to display the records −

mysql> SELECT * from TrailingANdLeadingDemo;

The following is the output

+----------------+
| SpaceTrailLead |
+----------------+
| john           |
+----------------+
1 row in set (0.00 sec)

Look at the above output, the spaces have been removed clearly from the column.