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