USE tempdb;
GO
CREATE TABLE dbo.SpaceTest
    (
    Data VARCHAR(4000) NOT NULL
    );
GO
INSERT  dbo.SpaceTest
SELECT  TOP (10000)
        SPACE(4000)
FROM    master.sys.allocation_units A1,
        master.sys.allocation_units A2,
        master.sys.allocation_units A3,
        master.sys.allocation_units A4;
GO
DECLARE
    @BitBucket VARCHAR(4000);

SET STATISTICS TIME ON

-- SLOW
SELECT 
    @BitBucket = --dbo.fn_CleanUp_MichaelMeierruth_MKII(Data)
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Data COLLATE LATIN1_GENERAL_CI_AS,
      '                                 ',' '),
      '                 ',' '),
      '         ',' '),
      '     ',' '),
      '   ',' '),
      '  ',' '),
      '  ',' ')
FROM 
    dbo.SpaceTest;
    
-- FAST
SELECT 
    @BitBucket = --dbo.fn_CleanUp_MichaelMeierruth_MKII(Data)
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Data COLLATE LATIN1_GENERAL_BIN,
      '                                 ',' '),
      '                 ',' '),
      '         ',' '),
      '     ',' '),
      '   ',' '),
      '  ',' '),
      '  ',' ')
FROM 
    dbo.SpaceTest;

SET STATISTICS TIME OFF;

DROP TABLE dbo.SpaceTest;

CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKIV(@S VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN   replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(replace(@s,CHAR(9),' '))),
  '                                 ',' '),
  '                 ',' '),
  '         ',' '),
  '     ',' '),
  '   ',' '),
  '  ',' '),
  '  ',' ')
END

CREATE FUNCTION dbo.fn_CleanUp_Recursion_Brigzy(@Data varchar(max))
RETURNS VarChar(Max)
AS 
BEGIN
	IF CHARINDEX('  ',@Data) > 0
	BEGIN
		SET @Data = REPLACE(@Data,'  ',' ')
		IF CHARINDEX('  ',@Data) > 0
			SELECT @Data = dbo.fn_CleanUp_Recursion_Brigzy(@Data)
	END
	RETURN @Data
END