从 MySQL 表中的值中修剪空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10886341/
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
Trim spaces from values in MySQL table
提问by Dimme
I want to update all the values in a table by trimming all the leading and trailing spaces. I have tried the following queries but neither worked.
我想通过修剪所有前导和尾随空格来更新表中的所有值。我尝试了以下查询，但都没有奏效。
I know that it is possible to use TRIM with SELECT, but how can I use it with UPDATE?
我知道可以将 TRIM 与 SELECT 一起使用，但是如何将它与 UPDATE 一起使用？
UPDATES teams SET name = TRIM(name)
UPDATES teams SET name = TRIM(LEADING ' ' TRAILING ' ' FROM name)
回答by JHS
You do not have to SELECT.
你不必SELECT。
Try this -
尝试这个 -
UPDATE teams SET name = TRIM(name)
WHERE 1 = 1;
回答by hannebaumsaway
UPDATE teams SET name = TRIM(name)
That should work, it is semantically correct for MySQL.
这应该有效，它在语义上对 MySQL 是正确的。
回答by Bugfixer
If field contain new line character then
如果字段包含换行符，则
UPDATE Tablename SET colname= TRIM(TRAILING '\r' FROM colname)
回答by Patrice Peyre
This one solves a weird problem I was having when the TRIM option does not work:
这个解决了我在 TRIM 选项不起作用时遇到的一个奇怪问题：
UPDATE
teams
SET
name = TRIM(BOTH UNHEX('C2A0') FROM name)
