CODEWITHSUNDEEP
mysql
Meet
Meet

Reputation: 21

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

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

Upvotes: 2

Views: 2163

Answers (2)

Johan
Johan

Reputation: 76753

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

Upvotes: 2

CD001
CD001

Reputation: 8482

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...

Upvotes: 1

Related Questions