ALTER FUNCTION dbo.fn_CleanUp(@FooString VARCHAR(max))
RETURNS VARCHAR(max)
BEGIN
	WHILE CHARINDEX('  ',@FooString) > 0
		SELECT @FooString = REPLACE(@FooString,'  ',' ')

	RETURN @FooString
END

GO 
-- Example
DECLARE @sample VARCHAR(1000)
SET @sample= 'ALKSDKLKJ                                         LSJD ASD  LSD S D    DJD D D D   D  D  D'

SELECT dbo.fn_CleanUp(@sample)

Richard Briggs (11/16/2009)

---

So we have to loop to get around 6 character limit of REPLACE:
I cant understand why REPLACE has that 6 char limit though?

I think the 6 char limit is a MS bug!

REPLACE(REPLACE(@sample,'  ',' '),'  ',' ')

REPLACE(@sample,'    ',' ')

WHILE CHARINDEX('  ', @sample) > 0
BEGIN
SET @sample = REPLACE(@sample,'  ',' ')
END

SELECT REPLACE(@sample,'  ','')

REPLACE(expression, ' ', ' ')