MySQL : Remove whitespaces from entire column

This article will see how to remove the whitespaces from all the column values in a MySQL column.

Table of Contents

• Remove all the whitespaces from the entire column values
• Remove the whitespaces from the start or end of the entire column values

Let us get started by making the sample data table named student_name_details and inserting some rows into it.

#create the table student_name_details
CREATE TABLE student_name_details (
student_id INT,
student_name VARCHAR(255)
);
#insert rows into student_name_details
INSERT INTO student_name_details(student_id,student_name) 
VALUES(1," Dani el "),
(2," Sandy"),
(3,"Rym a"),
(4,"Jenni fer"),
(5," Austin "),
(6,"Geor ge "),
(7,"Veroni ca");

View a snapshot of the table student_name_details by executing:

Output:-

Output in image_1 shows that values in column student_name have whitespaces in between, start, and end of the value. Let us move ahead to see how to remove all the whitespaces for the entire column values.

Remove all the whitespaces from the entire column values

This section will remove all the whitespaces from the values of column student_name. We will be using the replace() function as replace() function will remove the white spaces from between, start, and the end of the string value. 

Syntax:-

UPDATE tableName SET columnName = REPLACE(columnName, 'charactersToBeReplaced', 'charactersToBeReplacedWith');

Observe the below query as an example illustration.

UPDATE student_name_details SET student_name = REPLACE(student_name, ' ', '');

Action Output Message:-

13:49:36 UPDATE student_name_details SET student_name = REPLACE(student_name, ‘ ‘, ”) 7 row(s) affected Rows matched: 7 Changed: 7 Warnings: 0 0.012 sec

Let us see if the whitespaces were removed by executing:

SELECT * FROM student_name_details;

Output:-

Explanation:-

Output in image_2 shows that all the whitespaces from the column student_name are removed. The replace() function in our query is finding all the whitespaces and replacing them with none.

Remove the whitespaces from start or end of the entire column values

This section will remove the whitespaces, either at the start or the end of the values from column student_name. We will be using the trim() function.

Syntax:-

UPDATE tableName SET columnName = TRIM([{BOTH | LEADING | TRAILING} [charactersToBeRemoved] FROM ] columnName);

Observe the below query as an example illustration.

UPDATE student_name_details SET student_name = TRIM(' ' FROM student_name);

Action Output Message:-

16:27:45 UPDATE student_name_details SET student_name = TRIM(‘ ‘ FROM student_name) 0 row(s) affected Rows matched: 7 Changed: 0 Warnings: 0 0.0012 sec

Let us see if the leading and trailing whitespaces were removed by executing:

SELECT * FROM student_name_details;

Output:-

Explanation:-

Note that the query was applied to the original data of the table student_name_details as shown in image_1. The output in image_3 shows that all the whitespaces from the beginning and end of each value are removed, but there are still whitespaces between the values. Trim() function will remove only the leading, trailing characters, whitespaces in our example.

READ MORE: MySQL: Remove characters from string

We hope this article helped you with removing the white spaces from the values of a column. Good Luck!!!