How to remove leading and trailing whitespace from a MySQL field value?

We can remove the leading and trailing whitespaces from MySQL with the help of trim() function.

The following is the syntax.

mysql> SELECT TRIM(' AnyStringWithWhitespaces ');

Let us now implement the above syntax in the below query.

mysql> SELECT TRIM(' Leading And Trailing whitespaces Demo ');

Here is the output that removes the whitespaces.

+---------------------------------------+
| TRIM(' Leading And Trailing whitespaces Demo ') |
+---------------------------------------+
| Leading And Trailing whitespaces Demo |
+---------------------------------------+
1 row in set (0.00 sec)

Let us now see another way to remove the leading and trailing whitespaces.

Firstly, let us create a new table.

mysql> create table TrimDemo2
-> (
-> name varchar(200)
-> );
Query OK, 0 rows affected (0.53 sec)

Inserting a record with whitespace.

mysql> insert into TrimDemo2 values(' John ');
Query OK, 1 row affected (0.13 sec)

Displaying the record.

mysql> select *from TrimDemo2;

Here is the output.

+--------------------+
| name               |
+--------------------+
| John                |
+--------------------+
1 row in set (0.00 sec)

Let us now see the query to remove the leading and trailing whitespace.

mysql> UPDATE TrimDemo2 set name = TRIM(name);
Query OK, 1 row affected (0.20 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Here is the implementation.

mysql> select *from TrimDemo2;

The following is the output that shows the leading and trailing whitespaces are now removed.

+------+
| name |
+------+
| John |
+------+
1 row in set (0.00 sec)

George John

Published on 20-Nov-2018 12:56:52