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

Step through the characters one by one, and maintain a record of the previous character. If the current character is a space, and the last character is a space, stuff it.

CREATE FUNCTION [dbo].[fnRemoveExtraSpaces]  (@Number AS varchar(1000))
Returns Varchar(1000)
As
Begin
Declare @n int  -- Length of counter
Declare @old char(1)

Set @n = 1
--Begin Loop of field value
While @n <=Len (@Number)
    BEGIN
     If Substring(@Number, @n, 1) = ' ' AND @old = ' '
      BEGIN
        Select @Number = Stuff( @Number , @n , 1 , '' )
      END
     Else
      BEGIN
       SET @old = Substring(@Number, @n, 1)
       Set @n = @n + 1
      END
    END
Return @number
END
GO


select [dbo].[fnRemoveExtraSpaces]('xxx     xxx     xxx    xxx')

Answer 2

create table blank(
field_blank char(100))

insert into blank values('yyy      yyyy')
insert into blank values('xxxx    xxxx')
insert into blank values ('xxx    xxx')
insert into blank values ('zzzzzz zzzzz')

update blank 
set field_blank = substring(field_blank,1,charindex(' ',field_blank)-1) + ' ' + ltrim(substring(field_blank,charindex(' ',field_blank) + 1,len(field_blank)))
where CHARINDEX (' ' , rtrim(field_blank)) > 1

select * from blank

Answer 3

Not very SET Based but a simple WHILE would do the trick.

CREATE TABLE #myTable (myTextColumn VARCHAR(32))

INSERT INTO #myTable VALUES ('NoSpace')
INSERT INTO #myTable VALUES ('One Space')
INSERT INTO #myTable VALUES ('Two  Spaces')
INSERT INTO #myTable VALUES ('Multiple    Spaces    .')

WHILE EXISTS (SELECT * FROM #myTable WHERE myTextColumn LIKE '%  %')
  UPDATE  #myTable 
  SET     myTextColumn = REPLACE(myTextColumn, '  ', ' ') 
  WHERE   myTextColumn LIKE '%  %'

SELECT * FROM #myTable

DROP TABLE #myTable

Answer 4

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 5

SELECT 'starting...' --sets @@rowcount
WHILE @@rowcount <> 0
    update myTable
    set myTextColumn = replace(myTextColumn, '  ', ' ')
    where myTextColumn like '%  %'

Answer 6

Update myTable set myTextColumn = replace(myTextColumn, ' ', ' ');

The above query will remove all the double blank spaces with single blank space

But this would work only once.

Answer 7

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.

e.g.

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

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