REMOVE TAB, NEW LINE AND RETURN CARRIAGE FROM STRING (T-SQL)

I've been facing this issue for many years now and it came back again this morning. Actually, here is presented a problem dealing with character field that consist of mysterious "hidden" blank spaces.
Also, this might cause some troubles in while copying data and bringing it over to some other applications like MS Excel.

Well, here is the whole investigation and the solution that I've been using:

DECLARE @testString varchar(255)

set @testString = 'MY STRING      '

 /*Select the string and try to copy and paste into notepad and tab is still there*/

SELECT testString = @testString

/*Ok, it seems easy, let's try to trim this. Huh, it doesn't work, the same result here.*/

SELECT testStringTrim = RTRIM(@testString)

/*Let's try to get the size*/

SELECT LenOfTestString = LEN(@testString)

/*This supposed to give us string together with blank space, but not for tab though*/

SELECT DataLengthOfString= DATALENGTH(@testString)

SELECT ASCIIOfTab = ASCII(' ')

SELECT CHAR(9)

/*I always use this like a final solution*/

SET @testString = REPLACE(REPLACE(REPLACE(@testString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') SELECT @testString  

/*

CHAR(9)       - Tab

CHAR(10) - New Line

CHAR(13) - Carriage Return

*/

Mike C.