SQL method to replace repeating blanks with single blanks

Question

Is there a more elegant way of doing this. I want to replace repeating blanks with single blanks....

   declare @i int      set @i=0     while @i <= 20     begin         update myTable         set myTextColumn = replace(myTextColumn, '  ', ' ')         set @i=@i+1     end 

(its sql server 2000 - but I would prefer generic SQL)

Answer 1

This works:

UPDATE myTable SET myTextColumn =     REPLACE(         REPLACE(             REPLACE(myTextColumn                 ,'  ',' '+CHAR(1)) -- CHAR(1) is unlikely to appear         ,CHAR(1)+' ','')     ,CHAR(1),'') WHERE myTextColumn LIKE '%  %' 

Entirely set-based; no loops.

So we replace any two spaces with an unusual character and a space. If we call the unusual character X, 5 spaces become: ' X X ' and 6 spaces become ' X X X'. Then we replace 'X ' with the empty string. So 5 spaces become ' ' and 6 spaces become ' X'. Then, in case there was an even number of spaces, we remove any remaining 'X's, leaving a single space.

Answer 2

Here is a simple set based way that will collapse multiple spaces into a single space by applying three replaces.

DECLARE @myTable TABLE (myTextColumn VARCHAR(50))  INSERT INTO @myTable VALUES ('0Space') INSERT INTO @myTable VALUES (' 1 Spaces 1 Spaces. ') INSERT INTO @myTable VALUES ('  2  Spaces  2  Spaces.  ') INSERT INTO @myTable VALUES ('   3   Spaces  3   Spaces.   ') INSERT INTO @myTable VALUES ('    4    Spaces  4    Spaces.    ') INSERT INTO @myTable VALUES ('     5     Spaces  5     Spaces.     ') INSERT INTO @myTable VALUES ('      6      Spaces  6      Spaces.      ')  select replace(           replace(              replace(                 LTrim(RTrim(myTextColumn)), ---Trim the field              '  ',' |'),                    ---Mark double spaces           '| ',''),                         ---Delete double spaces offset by 1        '|','')                              ---Tidy up        AS SingleSpaceTextColumn  from @myTable 

Your Update statement can now be set based:

 update @myTable     set myTextColumn = replace(                           replace(                              replace(                                 LTrim(RTrim(myTextColumn)),                              '  ',' |'),                           '| ',''),                        '|','')   

Use an appropriate Where clause to limit the Update to only the rows that have you need to update or maybe have double spaces.

Example:

where 1<=Patindex('%  %', myTextColumn) 

I have found an external write up on this method: REPLACE Multiple Spaces with One