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 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.
773 Views
