MySql Procedure or function to replace mutiple spaces to single space in a string

Question

How can I replace multiple spaces to a single space in a mysql string.

Answer 1

DELIMITER $$

CREATE PROCEDURE I_hate_duplicate_spaces
BEGIN
  DECLARE rows_affected INTEGER;

  REPEAT
    UPDATE table1 SET afield = REPLACE(afield,'  ',' ');
    SELECT ROW_COUNT() INTO rows_affected;
  UNTIL rows_affected = 0 END REPEAT;
END $$

DELIMITER ;

Now start the stored procedure:

CALL I_hate_duplicate_spaces;

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

Answer 2

Essentially you want to do a regular expression replacement in MySQL something like PHP's preg_replace('/ +/', ' ', $sString) - unfortunately this isn't built into MySQL so you'd need a user-defined function ... which led me to this: How to do a regular expression replace in MySQL?

Hope that helps...