CODEWITHSUNDEEP
mysql
Jae Kun Choi
Jae Kun Choi

Reputation: 2029

MySQL remove all whitespaces from the entire column

Is there a way to remove all whitespaces from a specific column for all values?

Upvotes: 133

Views: 238303

Answers (7)

PAUL'S HOW TO
PAUL'S HOW TO

Reputation: 1

this works on me

UPDATE table SET col_name= TRIM(REPLACE(REPLACE(REPLACE(col_name, '\r', ' '), '\n', ' '), '\t', ' '));

Upvotes: 0

DJafari
DJafari

Reputation: 13545

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

and if you want remove all type of spaces, you can combine all of this functions :

UPDATE `table` SET `col_name` = REPLACE(REPLACE(REPLACE(`col_name`, ' ', ''), '\t', ''), '\n', '');

Upvotes: 277

Krysia
Krysia

Reputation: 1

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)

Upvotes: 0

Nomiluks
Nomiluks

Reputation: 2092

Just use the following sql, you are done:

SELECT replace(CustomerName,' ', '') FROM Customers;

you can test this sample over here: W3School

Upvotes: 1

emrhzc
emrhzc

Reputation: 1480

Since the question is how to replace ALL whitespaces

UPDATE `table` 
SET `col_name` = REPLACE
(REPLACE(REPLACE(`col_name`, ' ', ''), '\t', ''), '\n', '');

Upvotes: 19

Faisal
Faisal

Reputation: 4765

Using below query you can remove leading and trailing whitespace in a MySQL.

UPDATE `table_name`
SET `col_name` = TRIM(`col_name`);

Upvotes: 4

nrs jayaram
nrs jayaram

Reputation: 3448

Working Query:

SELECT replace(col_name , ' ','') FROM table_name;

While this doesn't :

SELECT trim(col_name) FROM table_name;

Upvotes: 12

Related Questions