How can we use MySQL TRIM() to remove spaces from all rows and update the table?

Suppose if a table has many values ​​that contain spaces in the columns of the table, then it is a waste of space. We can use TRIM() function to remove spaces from all rows and update the table in a single query. Following the example of "Employee" having spaces in all the rows will demonstrate 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)

From the above result set we can see that there are many rows in the Employee table Space. It can be deleted and updated using 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)

As you can see from the above result set, all the spaces have been removed and the table has been updated.

The above is the detailed content of How can we use MySQL TRIM() to remove spaces from all rows and update the table?. For more information, please follow other related articles on the PHP Chinese website!