Remove White Space Characters From A String In SQL Server

Trying to remove white spaces from a string in SQL but LTRIM and RTRIM functions don't seem to work?

|  COLUMN_0 | COLUMN_1 | LRTrim | LRLen | ASCIIR | ASCIIL | PRODUCTALTERNATEKEY |
---------------------------------------------------------------------------------
|  46783815 |        8 | 46783815|     8|   53   |   52   |            46783815 |
| 46783815  |        10|46783815  |   10|   10   |   52   |           46783815  |

Desired Result:

|  COLUMN_0 | COLUMN_1 | LRTrim | LRLen | ASCIIR | ASCIIL | PRODUCTALTERNATEKEY |
---------------------------------------------------------------------------------
|46783815|8|46783815|8|53|52|46783815|
|46783815|10|46783815|10|10|52|46783815|

Line Feeds, Carriage Returns and tabs are the usual culprits and can not be removed using the standard LTrim or RTrim Functions. You can remove them with the following:

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ProductAlternateKey, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')))

If you encounter more white space characters that can't be removed with the above then try one or all of the following:

--NULL
Replace([YourString],CHAR(0),'');
--Horizontal Tab
Replace([YourString],CHAR(9),'');
--Line Feed
Replace([YourString],CHAR(10),'');
--Vertical Tab
Replace([YourString],CHAR(11),'');
--Form Feed
Replace([YourString],CHAR(12),'');
--Carriage Return
Replace([YourString],CHAR(13),'');
--Column Break
Replace([YourString],CHAR(14),'');
--Non-breaking space
Replace([YourString],CHAR(160),'');

This list of potential white space characters could be used to create a function like:
CREATE FUNCTION [dbo].[Cleanandtrimstring] (@MyString AS VARCHAR(max))
returns VARCHAR(max)
AS
  BEGIN
      --NULL
      SET @MyString = Replace(@MyString, Char(0), '');
      --Horizontal Tab
      SET @MyString = Replace(@MyString, Char(9), '');
      --Line Feed
      SET @MyString = Replace(@MyString, Char(10), '');
      --Vertical Tab
      SET @MyString = Replace(@MyString, Char(11), '');
      --Form Feed
      SET @MyString = Replace(@MyString, Char(12), '');
      --Carriage Return
      SET @MyString = Replace(@MyString, Char(13), '');
      --Column Break
      SET @MyString = Replace(@MyString, Char(14), '');
      --Non-breaking space
      SET @MyString = Replace(@MyString, Char(160), '');
      SET @MyString = Ltrim(Rtrim(@MyString));

      RETURN @MyString
  END

go

Which you could then use as follows:

SELECT dbo.Cleanandtrimstring(columnname) AS ColumnName
FROM   tablename

sql ltrim rtrim trim mssql