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 ;
Related videos on Youtube
Author by
Kannan
Updated on June 04, 2022Comments
-
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 about 8 yearsThanks. UPDATE
table
SETfield
= REPLACE(field
, ' ', ''); is not working for more than two or three spaces. -
anna about 8 yearsUse a stored procedure to execute the query until there is no whitespace left.