Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

14.1. Clearing out Spaces in a String

This problem comes up on newsgroups about once a year. Given a VARCHAR (n) column with words in it, how do you squeeze out the extra spaces so that each word is separated by only one space? You can assume that you have a REPLACE (<target string>, <old string>, <new string>) function and a SPACES (n) function.

14.1.1. Procedural Solution #1

The obvious procedural code is a loop.

BEGIN
DECLARE i INTEGER;
SET i : (SELECT DATALENGTH(col_x) FROM  Foobar);
WHILE i > 1
   DO UPDATE Foobar
         SET col_x = REPLACE (col_x, SPACES(i), SPACES(1));
    SET i = i - 1;
END WHILE;
END;

I have seen code like this in production. It is quick and easy to write, but it keeps doing UPDATE statements that require table locking, logging, and ...