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
MySQL update query to remove spaces?
You can use TRIM() function to remove spaces. The syntax is as follows −
UPDATE yourTableName SET yourColumnName=TRIM(yourColumnName);
To understand the above syntax, let us create a table. The query to create a table is as follows −
mysql> create table removeSpaceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserId varchar(20), -> UserName varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.81 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into removeSpaceDemo(UserId,UserName) values('John1267 ','John');
Query OK, 1 row affected (0.25 sec)
mysql> insert into removeSpaceDemo(UserId,UserName) values(' 12Larry56','Larry');
Query OK, 1 row affected (0.17 sec)
mysql> insert into removeSpaceDemo(UserId,UserName) values(' David909','David');
Query OK, 1 row affected (0.18 sec)
mysql> insert into removeSpaceDemo(UserId,UserName) values('Bob912 ','Bob');
Query OK, 1 row affected (0.13 sec)
mysql> insert into removeSpaceDemo(UserId,UserName) values(' Sam123 ','Sam');
Query OK, 1 row affected (0.10 sec)Now you can display all records from the table using select statement. The query is as follows −
mysql> select *from removeSpaceDemo;
The following is the output displaying records with spaces:
+----+----------------+----------+ | Id | UserId | UserName | +----+----------------+----------+ | 1 | John1267 | John | | 2 | 12Larry56 | Larry | | 3 | David909 | David | | 4 | Bob912 | Bob | | 5 | Sam123 | Sam | +----+----------------+----------+ 5 rows in set (0.00 sec)
Here is the query to remove spaces −
mysql> update removeSpaceDemo set UserId=TRIM(UserId); Query OK, 5 rows affected (0.33 sec) Rows matched: 5 Changed: 5 Warnings: 0
Now check the table records once again. The query is as follows −
mysql> select *from removeSpaceDemo;
The following is the output −
+----+-----------+----------+ | Id | UserId | UserName | +----+-----------+----------+ | 1 | John1267 | John | | 2 | 12Larry56 | Larry | | 3 | David909 | David | | 4 | Bob912 | Bob | | 5 | Sam123 | Sam | +----+-----------+----------+ 5 rows in set (0.00 sec)
You can use RTRIM() function also in place of TRIM(). The syntax is as follows −
UPDATE yourTableName SET yourColumnName=RTRIM(yourColumnName);
Updated on: 30-Jul-2019
1K+ Views
Advertisements