Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 26-Jun-2020
1K+ Views
Advertisements