Remove tabs,new line etc from MySQL table

15,262
UPDATE `table` SET `field` = REPLACE(`field`, ' ', '');
UPDATE `table` SET `field` = REPLACE(`field`, '\t', '' );
UPDATE `table` SET `field` = REPLACE(`field`, '\n', '');
UPDATE `table` SET `field` = REPLACE(`field`, '\r', '');

or

UPDATE `table` set `field` = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(`field`,' ',''),'\t',''),'\n',''),'\r',''));

or create the following stored procedure and call it to remove more that one whitespace characters in a row

DELIMITER |
    CREATE PROCEDURE myProc()
    BEGIN 
      DECLARE affected INT DEFAULT 1; 
       WHILE affected > 0 DO
         UPDATE `table` set `field` = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(`field`,' ',''),'\t',''),'\n',''),'\r',''));
         SELECT ROW_COUNT() INTO affected;  
       END WHILE; 
    END |
DELIMITER ;  
Share:
15,262

Related videos on Youtube

Kannan
Author by

Kannan

Updated on June 04, 2022

Comments

  • Kannan
    Kannan over 1 year

    I need to remove multi spaces, tab, new line, carriage return, form feed or vertical tab from MySQL table's field.

    Here How to remove duplicate white spaces in string using Java?

    yourString = yourString.replaceAll("\\s+", " ");
    

    I found the result what I have expected but in JAVA. I need the same result in MySQL.

    Also I need to replace all single quotes to double quotes.

    How to do it .

  • Kannan
    Kannan about 8 years
    Thanks. UPDATE table SET field = REPLACE(field, ' ', ''); is not working for more than two or three spaces.
  • anna
    anna about 8 years
    Use a stored procedure to execute the query until there is no whitespace left.