Trim Trailing Whitespace, Tab, Newline, Carriage Returns, etc

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?

The best way would probably be to create a CLR function and use .NET framework libraries.

A TSQL attempt is below.

DECLARE @Pattern NVARCHAR (50) = N'%[^' + 
/*List from http://stackoverflow.com/a/18169122/73226 */
NCHAR(0x0009) +  /*tab*/
NCHAR(0x000A) +  /*line feed*/
NCHAR(0x000B) +  /*line tabulation*/
NCHAR(0x000C) +  /*form feed*/
NCHAR(0x000D) +  /*carriage return*/
/*Various spaces from https://www.cs.tut.fi/~jkorpela/chars/spaces.html */
NCHAR(0x0020) +  
NCHAR(0x00A0) +  
NCHAR(0x1680) +  
NCHAR(0x180E) +  
NCHAR(0x2000) +  
NCHAR(0x2001) +  
NCHAR(0x2002) +  
NCHAR(0x2003) +  
NCHAR(0x2004) +  
NCHAR(0x2005) +  
NCHAR(0x2006) +  
NCHAR(0x2007) +  
NCHAR(0x2008) +  
NCHAR(0x2009) +  
NCHAR(0x200A) +  
NCHAR(0x200B) +  
NCHAR(0x202F) +  
NCHAR(0x205F) +  
NCHAR(0x3000) +  
NCHAR(0xFEFF) +
N']%' COLLATE Latin1_General_100_BIN2;

WITH T(String) AS
(
SELECT '         leading only' union all
SELECT '

Line breaks

' union all
SELECT '    tab and space    ' union all
SELECT '' 
)
SELECT '[' + SUBSTRING(String,Start,len(String + '..') - Start - Finish) + ']'
FROM T
CROSS APPLY
(
SELECT  PATINDEX(@Pattern,String),
        PATINDEX(@Pattern,REVERSE(String))
)ca(Start, Finish)

The [ and ] delimiters are applied just to make it easier to verify there is no white space lurking there. Remove these when you're happy this is the case.

StackExchange Database Administrators Q#137200, answer score: 7