MySQL 从整个列中删除所有空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7313803/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL remove all whitespaces from the entire column
提问by Jae Kun Choi
Is there a way to remove all whitespaces from a specific column for all values?
有没有办法从所有值的特定列中删除所有空格？
回答by DJafari
To replace all spaces:
替换all spaces：
UPDATE `table` SET `col_name` = REPLACE(`col_name`, ' ', '')
To remove all tabscharacters :
删除所有tabs字符：
UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\t', '' )
To remove all new linecharacters :
删除所有new line字符：
UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\n', '')
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace
To remove first and last space(s)of column :
删除first and last space(s)列：
UPDATE `table` SET `col_name` = TRIM(`col_name`)
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
回答by emrhzc
Since the question is how to replace ALL whitespaces
由于问题是如何替换所有空格
UPDATE `table`
SET `col_name` = REPLACE
(REPLACE(REPLACE(`col_name`, ' ', ''), '\t', ''), '\n', '');
回答by 151291
Working Query:
工作查询：
SELECT replace(col_name , ' ','') FROM table_name;
SELECT replace(col_name , ' ','') FROM table_name;
While this doesn't :
虽然这不是：
SELECT trim(col_name) FROM table_name;
SELECT trim(col_name) FROM table_name;
回答by Faisal
Using below query you can remove leading and trailing whitespace in a MySQL.
使用以下查询，您可以删除 MySQL 中的前导和尾随空格。
UPDATE `table_name`
SET `col_name` = TRIM(`col_name`);
