MySQL: TRIM spaces and newlines from both sides

Question

MySQL has a built-in function TRIM() that can remove spaces and newlines one at a time from both sides of a string. However, to remove alternate spaces and newlines (ex: ' \r\n \r \n foo bar \r\n \r \n ') would require several loops of TRIM, which could be done via WHILE DO in a function:

DELIMITER $$
CREATE DEFINER=`localhost` FUNCTION `trim_all`(`_s1` TEXT) RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
    NO SQL
BEGIN

    DECLARE _s2 TEXT DEFAULT '';

    WHILE( CHAR_LENGTH(_s1) != CHAR_LENGTH(_s2) ) DO
        SET _s2 = _s1;
        SET _s1 = TRIM(TRIM('\r' FROM TRIM('\n' FROM _s1)));
    END WHILE;

    RETURN _s1;

END$$
DELIMITER ;

Although this function works (so far), it seems to be an over complication for such a simple task. Besides, such WHILE DO loops can be dangerous.

Questions

1. Any idea how to optimize this code? Any foreseen bug?
2. Any other idea how to do such trim without WHILE DO loops? Regex?

 

---

Notes:

SHOW VARIABLES LIKE "%version%"
version     5.6.39-83.1

Therefore, REGEXP_REPLACE is not available.

Answer 1

This seems like a better solution:

 UPDATE FOO set FIELD2 = TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r',''));

Copied from this other answer:

https://stackoverflow.com/a/23807798/236528