Replace duplicate spaces with a single space in T-SQL

Question

Even tidier:

select string = replace(replace(replace(' select   single       spaces',' ','<>'),'><',''),'<>',' ')

Output:

select single spaces

This would work:

declare @test varchar(100)
set @test = 'this   is  a    test'

while charindex('  ',@test  ) > 0
begin
   set @test = replace(@test, '  ', ' ')
end

select @test

If you know there won't be more than a certain number of spaces in a row, you could just nest the replace:

replace(replace(replace(replace(myText,'  ',' '),'  ',' '),'  ',' '),'  ',' ')

4 replaces should fix up to 16 consecutive spaces (16, then 8, then 4, then 2, then 1)

If it could be significantly longer, then you'd have to do something like an in-line function:

CREATE FUNCTION strip_spaces(@str varchar(8000))
RETURNS varchar(8000) AS
BEGIN 
    WHILE CHARINDEX('  ', @str) > 0 
        SET @str = REPLACE(@str, '  ', ' ')

    RETURN @str
END

Then just do

SELECT dbo.strip_spaces(myText) FROM myTable

This is somewhat brute force, but will work

CREATE FUNCTION stripDoubleSpaces(@prmSource varchar(max)) Returns varchar(max)
AS 
BEGIN
    WHILE (PATINDEX('%  %', @prmSource)>0)
     BEGIN
        SET @prmSource = replace(@prmSource  ,'  ',' ')
     END

    RETURN @prmSource
END

GO

-- Unit test -- 
PRINT dbo.stripDoubleSpaces('single    spaces   only')

single spaces only

It can be done recursively via the function:

CREATE FUNCTION dbo.RemSpaceFromStr(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS
BEGIN
  RETURN (CASE WHEN CHARINDEX('  ', @str) > 0 THEN
    dbo.RemSpaceFromStr(REPLACE(@str, '  ', ' ')) ELSE @str END);
END

then, for example:

SELECT dbo.RemSpaceFromStr('some   string    with         many     spaces') AS NewStr

returns:

NewStr
some string with many spaces

Or the solution based on method described by @agdk26 or @Neil Knight (but safer)
both examples return output above:

SELECT REPLACE(REPLACE(REPLACE('some   string    with         many     spaces'
  , '  ', ' ' + CHAR(7)), CHAR(7) + ' ', ''), ' ' + CHAR(7), ' ') AS NewStr 
--but it remove CHAR(7) (Bell) from string if exists...

or

SELECT REPLACE(REPLACE(REPLACE('some   string    with         many     spaces'
  , '  ', ' ' + CHAR(7) + CHAR(7)), CHAR(7) + CHAR(7) + ' ', ''), ' ' + CHAR(7) + CHAR(7), ' ') AS NewStr
--but it remove CHAR(7) + CHAR(7) from string

How it works:

Caution:
Char/string used to replace spaces shouldn't exist on begin or end of string and stand alone.