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 can we use MySQL TRIM() to remove the whitespaces from all the rows and update table?
Suppose if a table has many values having whitespaces in the columns of a table then it is wastage of space. We can use TRIM() function to remove whitespaces from all the rows and update the table too in a single query. Following the example from ‘Employee’, having whitespaces in all its rows will exhibit the concept −
Example
mysql> Select * from Employee; +------+----------------------+----------------------+----------------------+ | Id | Name | Address | Department | +------+----------------------+----------------------+----------------------+ | 100 | Raman | Delhi | IT | | 101 | Mohan | Haryana | History | | 102 | Shyam | Chandigarh | ENGLISH | | 103 | Sukhjeet Singh | Patiala | Computer Engg. | | 104 | Bimal Roy | Calcutta | Computer Engg. | +------+----------------------+----------------------+----------------------+ 5 rows in set (0.00 sec)
We can see from the above result set that Employee table is having so many whitespaces in its rows. It can be removed and updated with the following query −
mysql> Update Employee SET Id = TRIM(Id), Name = TRIM(Name), Address = TRIM(Address), DEPARTMENT = TRIM(Department); Query OK, 5 rows affected (0.24 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> Select * from Employee; +------+----------------+------------+----------------+ | Id | Name | Address | Department | +------+----------------+------------+----------------+ | 100 | Raman | Delhi | IT | | 101 | Mohan | Haryana | History | | 102 | Shyam | Chandigarh | ENGLISH | | 103 | Sukhjeet Singh | Patiala | Computer Engg. | | 104 | Bimal Roy | Calcutta | Computer Engg. | +------+----------------+------------+----------------+ 5 rows in set (0.00 sec)
We can see from the above result set that all the whitespaces have been removed and the table is updated.
Updated on: 06-Feb-2020
162 Views
Advertisements