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

User · Accepted Answer

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

User · Answer

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  lt subject gt   --                     lt pattern gt   --                     lt replacement gt   --                     lt greedy gt   --                     lt minMatchLen gt   --                     lt maxMatchLen gt    -- where  --  lt subject gt  is the string to look in for doing the replacements --  lt pattern gt  is the regular expression to match against --  lt replacement gt  is the replacement string --  lt greedy gt  is TRUE for greedy matching or FALSE for non-greedy matching --  lt minMatchLen gt  specifies the minimum match length --  lt maxMatchLen gt  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  lt  1  1  minMatchLen       SET maxMatchLen   IF maxMatchLen  lt  1 OR maxMatchLen  gt  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   lt  gt      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  gt   1 AND startPos  lt   CHAR LENGTH subject        AND startPos   minMatchLen - 1  lt   CHAR LENGTH subject        AND   LEFT pattern  1        AND startPos  lt  gt  1        AND   RIGHT pattern  1                    AND startPos   maxMatchLen - 1  lt  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  gt   1 AND len  lt   maxMatchLen                  AND startPos   len - 1  lt   CHAR LENGTH subject                   AND   RIGHT pattern  1                                AND startPos   len - 1  lt  gt  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  lt   CHAR LENGTH subject  THEN       SET result   CONCAT result  RIGHT subject  CHAR LENGTH subject    1 - startPos        ELSEIF startInc   -1 AND startPos  gt   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

User · Answer

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

User · 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

User · Answer

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

User · Answer

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

User · Answer

You can use the following sql  UPDATE TABLE SET Column  replace Column

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   nbsp  nbsp  nbsp  nbsp  nbsp  nbsp query-string nbsp  nbsp  nbsp

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

Examples related to mysql

Examples related to field

Examples related to removing-whitespace