So we have to loop to get around 6 character limit of REPLACE:

I cant understand why REPLACE has that 6 char limit though?

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)

I think the 6 char limit is a MS bug!:-)

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015