O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required