[mysql] How to remove leading and trailing whitespace in a MySQL field?

I have a table with two fields (countries and ISO codes):

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?

I know its already accepted, but for thoses like me who look for "remove ALL whitespaces" (not just at the begining and endingof the string):

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

EDIT 2019/6/20 : Yeah, that's not good. The function returns the part of the string since "when the character space occured for the first time". So, I guess that saying this remove the leading and trailling whitespaces and returns the first word :

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

If you need to use trim in select query, you can also use regular expressions

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

return rows with field like '      query-string   '

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.

Hope I could help you with sharing my answer :)

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;

You can use the following sql, UPDATE TABLE SET Column= replace(Column , ' ','')

It seems none of the current answers will actually remove 100% of whitespace from the start and end of a string.

As mentioned in other posts, the default TRIM only 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 TRIM and 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.

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 replacer which can also do the job - and may come in useful for other reasons, as described in this blog post.

Demo

Rextester online demo. In particular, the last row shows the other methods failing but the regular expression method succeeding.

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;

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

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

ex. UPDATE member SET firstName = LTRIM(firstName);

To remove whitespaces in the right side of the field value

UPDATE table SETfield1 = RTRIM(field1);

ex. UPDATE member SET firstName = RTRIM(firstName);

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.

you can use ltrim or rtrim to clean whitespaces for the right or left or a string.