I am working with SQL Server 2008 and I am looking for a function like ltrim and rtrim which will also remove leading and trailing tabs, double spaces, carriage returns, line feeds, etc.

There are a number of functions out there but the ones I found all have limitations, e.g. truncate the string to 8000 characters. e.g. (according to some of the comments)

http://blog.sqlauthority.com/2008/10/10/sql-server-2008-enhenced-trim-function-remove-trailing-spaces-leading-spaces-white-space-tabs-carriage-returns-line-feeds/

One of the comments proposed a better solution but the - 1 causes an incorrect syntax error and I am not sure why.

CREATE FUNCTION dbo.SuperTrimLeft(@str varchar(MAX)) RETURNS varchar(MAX)
    AS
    BEGIN
    IF (ASCII(LEFT(@str, 1)) < 33) BEGIN
        SET @str = STUFF(@str, 1, PATINDEX('%[^'+CHAR(0)+'-'+CHAR(32)+']%', @str) – 1, ' ');
    END;

    RETURN @str;
END;

So my question is, what is the best approach to accomplish the task above?

I am working with SQL Server 2008 and I am looking for a function like ltrim and rtrim which will also remove leading and trailing tabs, double spaces, carriage returns, line feeds, etc.

There are a number of functions out there but the ones I found all have limitations, e.g. truncate the string to 8000 characters. e.g. (according to some of the comments):

SQL SERVER – 2008 – Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

One of the comments proposed a better solution but the - 1 causes an incorrect syntax error and I am not sure why.

CREATE FUNCTION dbo.SuperTrimLeft(@str varchar(MAX)) RETURNS varchar(MAX)
    AS
    BEGIN
    IF (ASCII(LEFT(@str, 1)) < 33) BEGIN
        SET @str = STUFF(@str, 1, PATINDEX('%[^'+CHAR(0)+'-'+CHAR(32)+']%', @str) – 1, ' ');
    END;

    RETURN @str;
END;

So my question is, what is the best approach to accomplish the task above?