如何删除 MySQL 字段中的前导和尾随空格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6858143/
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
How to remove leading and trailing whitespace in a MySQL field?
提问by KB.
I have a table with two fields (countries and ISO codes):
我有一个包含两个字段（国家和 ISO 代码）的表：
Table1
field1 - e.g. 'Afghanistan' (without quotes)
field2 - e.g. 'AF'(without quotes)
In some rows the second field has whitespace at the start and/or end, which is affecting queries.
在某些行中，第二个字段在开头和/或结尾处有空格，这会影响查询。
Table1
field1 - e.g. 'Afghanistan' (without quotes)
field2 - e.g. ' AF' (without quotes but with that space in front)
Is there a way (in SQL) to go through the table and find/replace the whitespace in field2?
有没有办法（在 SQL 中）遍历表并查找/替换 field2 中的空格？
回答by Chris Sim
A general answer that I composed from your answers and from other links and it worked for me and I wrote it in a comment is:
我根据您的答案和其他链接组成的一般答案对我有用，我在评论中写道：
UPDATE FOO set FIELD2 = TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r',''));
etc.
等等。
Because trim() doesn't remove all the white spaces so it's better to replace all the white spaces u want and than trim it.
因为 trim() 不会删除所有空格，所以最好替换你想要的所有空格而不是修剪它。
Hope I could help you with sharing my answer :)
希望我能帮助你分享我的答案:)
回答by amitchhajer
Please understand the use case before using this solution:
请在使用此解决方案之前了解用例：
trim does not work while doing select query
执行选择查询时修剪不起作用
This works
这有效
select replace(name , ' ','') from test;
While this doesn't
虽然这不
select trim(name) from test;
回答by mulya
Just to be clear, TRIM by default only remove spaces(not all whitespaces). Here is the doc: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
需要明确的是，默认情况下 TRIM仅删除空格（并非所有空格）。这是文档：http: //dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
回答by Steve Chambers
It seems none of the current answers will actually remove 100% of whitespace from the start and end of a string.
似乎当前的答案都不会真正从字符串的开头和结尾删除 100% 的空格。
As mentioned in other posts, the default TRIMonly removes spaces - not tabs, formfeeds etc. A combination of TRIMs specifying other whitespace characters may provide a limited improvement e.g. TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM TRIM(BOTH '\f' FROM TRIM(BOTH '\t' FROM TRIM(txt))))). But the problem with this approach is only a single character can be specified for a particular TRIMand those characters are only removed from the start and end. So if the string being trimmed is something like \t \t \t \t(i.e. alternate spaces and tab characters), more TRIMs would be needed - and in the general case this could go on indefinitely.
正如其他帖子中提到的，默认值TRIM仅删除空格 - 而不是制表符、换页符等。TRIMs 指定其他空白字符的组合可能会提供有限的改进，例如TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM TRIM(BOTH '\f' FROM TRIM(BOTH '\t' FROM TRIM(txt))))). 但是这种方法的问题是只能为特定TRIM字符指定单个字符，并且这些字符仅从开头和结尾删除。因此，如果要修剪的字符串类似于\t \t \t \t（即备用空格和制表符），则TRIM需要更多的s - 在一般情况下，这可能会无限期地持续下去。
For a lightweight solution, it should be possible to write a simple User Defined Function (UDF) to do the job by looping through the characters at the start and end of a string. But I'm not going to do that... as I've already written a rather more heavyweight regular expression replacerwhich can also do the job - and may come in useful for other reasons, as described in this blog post.
对于轻量级解决方案，应该可以编写一个简单的用户定义函数 (UDF) 来通过循环字符串开头和结尾的字符来完成这项工作。但我不会那样做……因为我已经写了一个更重量级的正则表达式替换器，它也可以完成这项工作 - 并且可能因其他原因而有用，如本博文所述。
Demo
演示
Rextester online demo. In particular, the last row shows the other methods failing but the regular expression method succeeding.
Rexester 在线演示。特别是，最后一行显示其他方法失败但正则表达式方法成功。
Function:
功能：
-- ------------------------------------------------------------------------------------
-- USAGE
-- ------------------------------------------------------------------------------------
-- SELECT reg_replace(<subject>,
-- <pattern>,
-- <replacement>,
-- <greedy>,
-- <minMatchLen>,
-- <maxMatchLen>);
-- where:
-- <subject> is the string to look in for doing the replacements
-- <pattern> is the regular expression to match against
-- <replacement> is the replacement string
-- <greedy> is TRUE for greedy matching or FALSE for non-greedy matching
-- <minMatchLen> specifies the minimum match length
-- <maxMatchLen> specifies the maximum match length
-- (minMatchLen and maxMatchLen are used to improve efficiency but are
-- optional and can be set to 0 or NULL if not known/required)
-- Example:
-- SELECT reg_replace(txt, '^[Tt][^ ]* ', 'a', TRUE, 2, 0) FROM tbl;
DROP FUNCTION IF EXISTS reg_replace;
CREATE FUNCTION reg_replace(subject VARCHAR(21845), pattern VARCHAR(21845),
replacement VARCHAR(21845), greedy BOOLEAN, minMatchLen INT, maxMatchLen INT)
RETURNS VARCHAR(21845) DETERMINISTIC BEGIN
DECLARE result, subStr, usePattern VARCHAR(21845);
DECLARE startPos, prevStartPos, startInc, len, lenInc INT;
IF subject REGEXP pattern THEN
SET result = '';
-- Sanitize input parameter values
SET minMatchLen = IF(minMatchLen < 1, 1, minMatchLen);
SET maxMatchLen = IF(maxMatchLen < 1 OR maxMatchLen > CHAR_LENGTH(subject),
CHAR_LENGTH(subject), maxMatchLen);
-- Set the pattern to use to match an entire string rather than part of a string
SET usePattern = IF (LEFT(pattern, 1) = '^', pattern, CONCAT('^', pattern));
SET usePattern = IF (RIGHT(pattern, 1) = '$', usePattern, CONCAT(usePattern, '$'));
-- Set start position to 1 if pattern starts with ^ or doesn't end with $.
IF LEFT(pattern, 1) = '^' OR RIGHT(pattern, 1) <> '$' THEN
SET startPos = 1, startInc = 1;
-- Otherwise (i.e. pattern ends with $ but doesn't start with ^): Set start position
-- to the min or max match length from the end (depending on "greedy" flag).
ELSEIF greedy THEN
SET startPos = CHAR_LENGTH(subject) - maxMatchLen + 1, startInc = 1;
ELSE
SET startPos = CHAR_LENGTH(subject) - minMatchLen + 1, startInc = -1;
END IF;
WHILE startPos >= 1 AND startPos <= CHAR_LENGTH(subject)
AND startPos + minMatchLen - 1 <= CHAR_LENGTH(subject)
AND !(LEFT(pattern, 1) = '^' AND startPos <> 1)
AND !(RIGHT(pattern, 1) = '$'
AND startPos + maxMatchLen - 1 < CHAR_LENGTH(subject)) DO
-- Set start length to maximum if matching greedily or pattern ends with $.
-- Otherwise set starting length to the minimum match length.
IF greedy OR RIGHT(pattern, 1) = '$' THEN
SET len = LEAST(CHAR_LENGTH(subject) - startPos + 1, maxMatchLen), lenInc = -1;
ELSE
SET len = minMatchLen, lenInc = 1;
END IF;
SET prevStartPos = startPos;
lenLoop: WHILE len >= 1 AND len <= maxMatchLen
AND startPos + len - 1 <= CHAR_LENGTH(subject)
AND !(RIGHT(pattern, 1) = '$'
AND startPos + len - 1 <> CHAR_LENGTH(subject)) DO
SET subStr = SUBSTRING(subject, startPos, len);
IF subStr REGEXP usePattern THEN
SET result = IF(startInc = 1,
CONCAT(result, replacement), CONCAT(replacement, result));
SET startPos = startPos + startInc * len;
LEAVE lenLoop;
END IF;
SET len = len + lenInc;
END WHILE;
IF (startPos = prevStartPos) THEN
SET result = IF(startInc = 1, CONCAT(result, SUBSTRING(subject, startPos, 1)),
CONCAT(SUBSTRING(subject, startPos, 1), result));
SET startPos = startPos + startInc;
END IF;
END WHILE;
IF startInc = 1 AND startPos <= CHAR_LENGTH(subject) THEN
SET result = CONCAT(result, RIGHT(subject, CHAR_LENGTH(subject) + 1 - startPos));
ELSEIF startInc = -1 AND startPos >= 1 THEN
SET result = CONCAT(LEFT(subject, startPos), result);
END IF;
ELSE
SET result = subject;
END IF;
RETURN result;
END;
DROP FUNCTION IF EXISTS format_result;
CREATE FUNCTION format_result(result VARCHAR(21845))
RETURNS VARCHAR(21845) DETERMINISTIC BEGIN
RETURN CONCAT(CONCAT('|', REPLACE(REPLACE(REPLACE(REPLACE(result, '\t', '\t'), CHAR(12), '\f'), '\r', '\r'), '\n', '\n')), '|');
END;
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl
AS
SELECT 'Afghanistan' AS txt
UNION ALL
SELECT ' AF' AS txt
UNION ALL
SELECT ' Cayman Islands ' AS txt
UNION ALL
SELECT CONCAT(CONCAT(CONCAT('\t \t ', CHAR(12)), ' \r\n\t British Virgin Islands \t \t ', CHAR(12)), ' \r\n') AS txt;
SELECT format_result(txt) AS txt,
format_result(TRIM(txt)) AS trim,
format_result(TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM TRIM(BOTH '\f' FROM TRIM(BOTH '\t' FROM TRIM(txt))))))
AS `trim spaces, tabs, formfeeds and line endings`,
format_result(reg_replace(reg_replace(txt, '^[[:space:]]+', '', TRUE, 1, 0), '[[:space:]]+$', '', TRUE, 1, 0))
AS `reg_replace`
FROM tbl;
Usage:
用法：
SELECT reg_replace(
reg_replace(txt,
'^[[:space:]]+',
'',
TRUE,
1,
0),
'[[:space:]]+$',
'',
TRUE,
1,
0) AS `trimmed txt`
FROM tbl;
回答by luxknight_007
This statement will remove and update the field content of your database
此语句将删除和更新数据库的字段内容
To remove whitespaces in the left side of the field value
删除字段值左侧的空格
UPDATE table SET field1 = LTRIM(field1);
更新表 SET field1 = LTRIM(field1);
ex. UPDATE member SET firstName = LTRIM(firstName);
前任。更新成员 SET firstName = LTRIM(firstName);
To remove whitespaces in the right side of the field value
删除字段值右侧的空格
UPDATE table SETfield1 = RTRIM(field1);
更新表 SETfield1 = RTRIM(field1);
ex. UPDATE member SET firstName = RTRIM(firstName);
前任。更新成员 SET firstName = RTRIM(firstName);
回答by MistyDawn
I needed to trim the values in a primary key column that had first and last names, so I did not want to trim all white space as that would remove the space between the first and last name, which I needed to keep. What worked for me was...
我需要修剪具有名字和姓氏的主键列中的值，所以我不想修剪所有空格，因为这会删除我需要保留的名字和姓氏之间的空格。对我有用的是...
UPDATE `TABLE` SET `FIELD`= TRIM(FIELD);
or
或者
UPDATE 'TABLE' SET 'FIELD' = RTRIM(FIELD);
or
或者
UPDATE 'TABLE' SET 'FIELD' = LTRIM(FIELD);
Note that the first instance of FIELD is in single quotes but the second is not in quotes at all. I had to do it this way or it gave me a syntax error saying it was a duplicate primary key when I had both in quotes.
请注意， FIELD 的第一个实例在单引号中，但第二个实例根本不在引号中。我必须这样做，否则它给了我一个语法错误，说当我在引号中时，它是一个重复的主键。
回答by TheSameSon
If you need to use trim in select query, you can also use regular expressions
如果需要在select查询中使用trim，也可以使用正则表达式
SELECT * FROM table_name WHERE field RLIKE ' * query-string *'
return rows with field like ' query-string '
返回带有“查询字符串”等字段的行
回答by Tomer
you can use ltrim or rtrim to clean whitespaces for the right or left or a string.
您可以使用 ltrim 或 rtrim 来清除右侧或左侧或字符串的空格。
回答by Optimus Prime
You can use the following sql,
UPDATE TABLESET Column= replace(Column , ' ','')
可以使用下面的sql，UPDATE TABLESET Column= replace(Column , ' ','')
