4

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
  • 63
  • 1
  • 1
  • 6
  • There does not appear to be a single MySQL function which can remove all whitespace characters in one go, q.v. [this SO post](http://stackoverflow.com/questions/7313803/mysql-remove-all-whitespaces-from-the-entire-column). – Tim Biegeleisen Oct 23 '15 at 07:10

2 Answers2

13
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 ;
anna
  • 585
  • 1
  • 6
  • 22
  • Thanks. UPDATE `table` SET `field` = REPLACE(`field`, ' ', ''); is not working for more than two or three spaces. – Kannan Oct 23 '15 at 07:17
  • Use a stored procedure to execute the query until there is no whitespace left. – anna Oct 23 '15 at 08:01
1

We had the same issue with the "VT" (Vertical Tab) character.

Following worked for us:

UPDATE `table` SET `field` = REPLACE(`field`, CHAR(11), '');

And to locate data with this character, we used this:

SELECT * FROM `table` WHERE INSTR(`field`, char(11)) > 0;
d3rbastl3r
  • 463
  • 1
  • 6
  • 16