如何在MySQL字段中去除前导和尾随空格？

Question

我有一张包含两个字段（国家和ISO代码）的表：

Table1

   field1 - e.g. 'Afghanistan' (without quotes)
   field2 - e.g. 'AF'(without quotes)

某些行的第二个字段开头和/或结尾存在空格，这会影响查询。

Table1

   field1 - e.g. 'Afghanistan' (without quotes) 
   field2 - e.g. ' AF' (without quotes but with that space in front)

在SQL中，是否有一种方法可以遍历表格并查找/替换字段2中的空格？

- KB.

---

将我的答案添加为注释以使其更明显：只有默认情况下TRIM才会删除空格（而非所有空格）。这是文档链接：http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim - mulya

Answer 1

你需要使用TRIM函数。

UPDATE FOO set FIELD2 = TRIM(FIELD2);

---

似乎值得一提的是 TRIM 支持多种类型的空格，但一次只能使用一种，默认情况下会使用空格。但是你可以嵌套 TRIM。

 TRIM(BOTH ' ' FROM TRIM(BOTH '\n' FROM column))

如果你真的想一次性删除所有空格，最好使用REGEXP_REPLACE和[[:space:]]符号。这是一个示例：

SELECT 
    -- using concat to show that the whitespace is actually removed.
    CONCAT(
         '+', 
         REGEXP_REPLACE(
             '    ha ppy    ', 
             -- This regexp matches 1 or more spaces at the beginning with ^[[:space:]]+
             -- And 1 or more spaces at the end with [[:space:]]+$
             -- By grouping them with `()` and splitting them with the `|`
             -- we match all of the expected values.
             '(^[[:space:]]+|[[:space:]]+$)', 

             -- Replace the above with nothing
             ''
         ), 
         '+') 
    as my_example;
-- outputs +ha ppy+

- cwallenpoole

---

请注意：这只会移除普通空格，而不是其他空白字符（制表符、换行符等）。 - TM.

没错，@TM。所以最好使用以下语句： UPDATE FOO SET FIELD2 = TRIM(REPLACE(REPLACE(REPLACE(FIELD2,'\t',''),'\n',''),'\r','')); 等等。 - Chris Sim

@ChrisSim的解决方案会替换内容中的换行符和制表符，这显然不是大多数人从TRIM函数所期望得到的结果！ - JoLoCo

UPDATE 表 T1 SET 字段 = (SELECT replace(字段, ' ', '') FROM 表 T2 WHERE T1.id = T2.id);由于 TRIM 的效果不尽如人意，因此这段代码应该能够正常运行。 - Maksoud Rodrigues

Answer 2

我从您的回答和其他链接中总结出了一般性答案，对于我来说它很有效，我在评论中写了它：

 UPDATE FOO set FIELD2 = TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r',''));

因为trim()函数不能移除所有的空格，所以最好先替换您想要的所有空格，然后再使用trim()函数。

希望我的回答能对您有所帮助 :)

- Chris Sim

---

这会删除所有制表符和换行符。TRIM应该只删除字符串两端的空格。 - DisgruntledGoat

这是一个很好的思路，可以去除新行字符，谢谢。非常有效，我已经点赞了，因为这个想法很棒。@Chris Sim - Sankar Ganesh PMP

Answer 3

需要澄清的是，默认情况下 TRIM 仅仅移除空格（而不是所有的空白字符）。这里是文档链接：http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

- mulya

---

这应该被添加为一条评论。 - Oytun

Answer 4

在使用此解决方案之前，请理解使用情况:

在执行select查询时，trim函数无法正常工作。

这个可以解决问题。

select replace(name , ' ','') from test;

虽然这样做不会

select trim(name) from test;

- amitchhajer

---

TRIM()在我的SELECT语句中很好用，我真的很好奇为什么这个答案赞数那么多。你是在使用MySQL吗？是哪个版本？ - But those new buttons though..

去除字符串前后空格，仅保留中间的内容。参考链接：http://dev.mysql.com/doc/refman/5.7/en/string-functions.html - amitchhajer

这个回答是错误的。这怎么会得到50多个赞？ - Loko

这不仅是错误的，而且很危险。它可能会严重破坏某人的数据。 - some-non-descript-user

我进行了负评。测试用例：SELECT CONCAT('"', TRIM(" hello world "), '"') AS \trimmed value` FROM DUAL 可以得到正确的输出结果"hello world"。而使用替换函数则会危险地移除空格，导致输出结果不符合预期：SELECT CONCAT('"', REPLACE(" hello world ", ' ', '')) AS `replaced value` FROM DUAL 输出的是错误的结果"helloworld"`。 - Piemol

Answer 5

似乎当前提供的答案都不能完全移除字符串开头和结尾的所有空格。

如其他帖子中所述，默认的 TRIM 仅删除空格，而不是制表符、换页符等。指定其他空白字符的一组 TRIM 的组合可能会有所改善，例如 TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM TRIM(BOTH '\f' FROM TRIM(BOTH '\t' FROM TRIM(txt)))))，但这种方法的问题在于每个 TRIM 只能指定单个字符，并且这些字符只能从开头和结尾处删除。因此，如果要修剪的字符串类似于 \t \t \t \t（即交替出现的空格和制表符字符），则需要更多的 TRIM - 在一般情况下，这可能永无止境。

对于轻量级解决方案，可以编写一个简单的用户定义函数（UDF）来完成此任务，通过循环遍历字符串开头和结尾的字符。但我不会这样做……因为我已经编写了一个相当沉重的正则表达式替换器，它也可以完成此任务，并且可能出于其他原因有用，如这篇博客文章所述。

演示

Rextester 在线演示。特别是，最后一行显示了其他方法失败，但正则表达式方法成功。

函数：

-- ------------------------------------------------------------------------------------
-- 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;

用法：

SELECT reg_replace(
         reg_replace(txt,
                     '^[[:space:]]+',
                     '',
                     TRUE,
                     1,
                     0),
         '[[:space:]]+$',
         '',
         TRUE,
         1,
         0) AS `trimmed txt`
FROM tbl;

- Steve Chambers

---

这真是令人印象深刻！在MySQL 8+中，可以使用新的REGEXP_REPLACE()函数。 - oriadam

Answer 6

这个语句将删除并更新数据库中字段的内容。

要删除字段值左侧的空格，请使用以下语句： UPDATE table SET field1 = LTRIM(field1); 例如：UPDATE member SET firstName = LTRIM(firstName);

要删除字段值右侧的空格，请使用以下语句： UPDATE table SET field1 = RTRIM(field1); 例如：UPDATE member SET firstName = RTRIM(firstName);

- luxknight_007

Answer 7

我需要修剪主键列中的值，该列包含名字和姓氏，因此我不想修剪所有的空格，因为这会删除名字和姓氏之间的空格，而我需要保留这个空格。对我有效的方法是...

UPDATE `TABLE` SET `FIELD`= TRIM(FIELD);

或者

UPDATE 'TABLE' SET 'FIELD' = RTRIM(FIELD);

或者

UPDATE 'TABLE' SET 'FIELD' = LTRIM(FIELD);

请注意，FIELD的第一个实例是用单引号括起来的，但第二个实例根本没有用引号括起来。我必须这样做，否则它会给我一个语法错误，说它是一个重复的主键，当我两个都用引号时。

- MistyDawn

Answer 8

如果您需要在查询中使用trim，也可以使用正则表达式。

SELECT * FROM table_name WHERE field RLIKE ' * query-string *'

返回字段类似于 '      查询字符串   ' 的行。

- TheSameSon

Answer 9

你可以使用ltrim或rtrim来清除字符串左侧或右侧的空格。

- Tomer

Answer 10

我知道它已经被接受了，但对于像我这样寻找“删除所有空格”（不仅仅是在字符串的开头和结尾）的人来说：

select SUBSTRING_INDEX('1234 243', ' ', 1);
// returns '1234'

编辑于2019/6/20： 嗯，那不太好。该函数返回自“第一次出现字符空格以来”的字符串部分。 所以，我想说这将删除前导和尾随空格并返回第一个单词：

select SUBSTRING_INDEX(TRIM(' 1234 243'), ' ', 1);

- François Breton

---

这与原帖无关。 - mickmackusa

哇，你并不是在移除所有的空格 - 你是在移除从第一个空格开始的所有内容。 - Timo