MySQL remove all whitespaces from the entire column
MysqlMysql Problem Overview
Is there a way to remove all whitespaces from a specific column for all values?
Mysql Solutions
Solution 1 - Mysql
To replace all spaces
:
UPDATE `table` SET `col_name` = REPLACE(`col_name`, ' ', '')
To remove all tabs
characters :
UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\t', '' )
To remove all new line
characters :
UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\n', '')
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace
To remove first and last space(s)
of column :
UPDATE `table` SET `col_name` = TRIM(`col_name`)
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
Solution 2 - Mysql
Since the question is how to replace ALL whitespaces
UPDATE `table`
SET `col_name` = REPLACE
(REPLACE(REPLACE(`col_name`, ' ', ''), '\t', ''), '\n', '');
Solution 3 - Mysql
Working Query:
SELECT replace(col_name , ' ','') FROM table_name;
While this doesn't :
SELECT trim(col_name) FROM table_name;
Solution 4 - Mysql
Using below query you can remove leading and trailing whitespace in a MySQL.
UPDATE `table_name`
SET `col_name` = TRIM(`col_name`);
Solution 5 - Mysql
Just use the following sql, you are done:
> SELECT replace(CustomerName,' ', '') FROM Customers;
you can test this sample over here: W3School
Solution 6 - Mysql
Note: there are two types of white spaces, those that come from the space bar and those that come from the tab button, you need to do a replace for both. I suspect this is why the TRIM function seems not to always work.
so
replace(replace(<field_name>,' ',''),' ','')
(there could be more types of white spaces, but these are the two I have found)