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

Question

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?

Faradmeter

Adding my answer as comment to make it more visible: Just to be clear, TRIM by default only remove spaces (not all whitespaces). Here is the doc: dev.mysql.com/doc/refman/5.0/en/… – Jacal

Answer 1

You're looking for TRIM.

UPDATE FOO set FIELD2 = TRIM(FIELD2);

---

Seems like it might be worth it to mention that TRIM can support multiple types of whitespace, but only one at a time and it will use a space by default. You can, however, nest TRIMs.

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

If you really want to get rid of all the whitespace in one call, you're better off using REGEXP_REPLACE along with the [[:space:]] notation. Here is an example:

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+

Hawking

Note: this only removes regular spaces and not other whitespace characters (tab, newline, etc) – Jeavons

yes you're right @TM so it's better to use: UPDATE FOO set FIELD2 = TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r','')); etc. – Woo

Though @ChrisSim's solution will replace newlines and tabs WITHIN the content too, of course -- which probably isn't what most people want from a TRIM function! – Turki

UPDATE table T1 SET field = (SELECT replace(field, ' ', '') FROM table T2 WHERE T1.id = T2.id); This should work since TRIM doesn't work as expected – Exudation

Answer 2

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

Woo

This removes all tabs/newlines. TRIM should only remove whitespace at either end of the string. – Eponym

this a good idea of thinking and removing the new line characters, thanks , Works like a charm,i have upvoted it , bcoz for this thinking @Chris Sim – Knife

Answer 3

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

Jacal

This should be added as a comment. – Chitwood

Answer 4

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;

Outclass

This should work: select trim(name) name from test; – Essary

TRIM() works fine for me in a SELECT statement, I'm really curious as to why this answer's got so many upvotes. Are you using mysql? What version? – Eisler

trim remove leading and trailing spaces only dev.mysql.com/doc/refman/5.7/en/string-functions.html – Outclass

This is not ideal for name containing two words with space separator. Ex:- "Hong Kong" will become "HongKong". – Immortelle

Yeah this answer is wrong. How did this get 50+ upvotes? – Uzzia

This is not only wrong, it's dangerous. It can seriously garble someone's data. – Grizel

I downvoted. Testcase: SELECT CONCAT('"', TRIM(" hello world "), '"') AS `trimmed value` FROM DUAL gives the wanted output "hello world". While the replace variant dangerously removes the space as word separator: SELECT CONCAT('"', REPLACE(" hello world ", ' ', '')) AS `replaced value` FROM DUAL gives the unwanted output "helloworld" – Russom

TRIM(name) does work, just only on CHAR(20) (the space character) unless specified differently. TRIM will remove only one specific character. – Adjust

Answer 5

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;

Gewgaw

that's pretty impressive! On MySQL 8+ one can use the new REGEXP_REPLACE() – Babylon

Answer 6

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

Rework

Answer 7

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.

Woodwind

Answer 8

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   '

Piercing

Answer 9

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

Icarus

Answer 10

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

Stenopetalous

This has no relevance to the OP. – Mickiemickle

Whoah, you're not removing all whitespaces - you are removing everything from the first space onward. – Dumah