here's how i would do it with an CTE Tally Table:

CREATE FUNCTION StripExtraSpaces(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

DECLARE @CleanedText VARCHAR(8000)

;WITH tally (N) as

(SELECT TOP 1000000 row_number() OVER (ORDER BY sc1.id)

FROM SysColumns sc1

CROSS JOIN SysColumns sc2

CROSS JOIN SysColumns sc3)

SELECT @CleanedText = ISNULL(@CleanedText,'') +

CASE

--ascii numbers are 48(for '0') thru 57 (for '9')

WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) = 32 --space

AND ASCII(SUBSTRING(@OriginalText,Tally.N -1 ,1)) = 32

THEN ''

ELSE SUBSTRING(@OriginalText,Tally.N,1)

END

FROM Tally

WHERE Tally.N <= LEN(@OriginalText)

RETURN @CleanedText

END

GO

select dbo.StripExtraSpaces('will this strip out extraa whitespace, ')

--results: yes it will:

[will this strip out extraa whitespace,]