SQL Server User Defined Function to collapse whitespace in a string.

create function dbo.CollapseWhitespace (
    @str varchar(max)
)
returns varchar(max)
as
begin
    if @str is null
    begin
        return null;
    end;

    --
    -- Normalize whitespace characters with a single space
    -- - char(9)  = horizontal tab
    -- - char(10) = line feed
    -- - char(11) = vertical tab
    -- - char(13) = carriage return
    set @str = replace(replace(replace(replace(@str, char(9), ' '), char(10), ' '), char(11), ' '), char(13), ' ');

    --
    -- Replace duplicate whitespaces witha single space
    while charindex('  ', @str) > 0
    begin
        set @str = replace(@str, '  ', ' ');
    end;

    --
    -- Trim edges
    return ltrim(rtrim(@str));
end;