Collapse Spaces Function

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[fn_collapse_spaces]') and type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    begin
        drop function dbo.fn_collapse_spaces
    end;
go

create function dbo.fn_collapse_spaces
(
    @str varchar(max)
)
returns varchar(max)
as
    begin
    -- ========================================================================
    -- @name fn_collapse_spaces
    -- @type User Defined Function
    -- @description Reduce multiple contiguous spaces, into a single space.
    -- @str varchar(max) The string containing multiple spaces.
    -- @ref http://www.sqlusa.com/bestpractices2005/removemultiplespaces/
    -- ========================================================================
        while charindex('  ', @str) > 0
            set @str = replace(@str, '  ', ' ');
        return @str;
    end;
go