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

• Related Questions & Answers
• How to remove leading and trailing whitespace in a MySQL field?
• Java Program to remove leading and trailing whitespace from a string
• How to remove all trailing and leading whitespace in a string in Python?
• Python Pandas – Remove leading and trailing whitespace from more than one column
• How to remove white spaces (leading and trailing) from string value in MongoDB?
• Java Program to remove the leading and trailing quotes from a string
• Trim a string in Java to remove leading and trailing spaces
• Trim (Remove leading and trailing spaces) a string in C#
• How to remove all trailing whitespace of string in Python?
• How to remove all leading whitespace in string in Python?
• How can we eradicate leading and trailing space characters from a string in MySQL?
• How can I remove the leading and trailing spaces both at once from a string by using MySQL LTRIM() and RTRIM() functions?
• How can I remove the leading and trailing spaces both at once from a string without using MySQL LTRIM() and RTRIM() functions?
• Remove trailing numbers surrounded by parenthesis from a MySQL column
• MySQL query to remove trailing spaces