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

Table of Contents

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:-

image_1 : student_name_details

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');
NameDescription
tableNameName of the table.
columnNameName of the column whose values are to be updated.
charactersToBeReplacedThe characters to be removed from each value.
charactersToBeReplacedWithThe new characters which will be part of each column value instead of charactersToBeReplaced.
table_1

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:-

image_2

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);
NameDescription
tableNameName of the table.
columnNameName of the column whose values are to be updated.
charactersToBeRemovedThe characters to be removed from each value.
BOTH, LEADING,TRAILING BOTH: used when we want to remove characters from beginning and end.
LEADING: used when we want to remove characters from the beginning.
TRAILING: used when we want to remove characters from the end.
BOTH/LEADING/TRAILING is optional.
** If none of the parameters are present, BOTH is applied by default.
table_2

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:-

image_3

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!!!
You need to add a widget, row, or prebuilt layout before you’ll see anything here. 🙂