Replace duplicate spaces with a single space in T-SQL

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