How to remove invisible characters in t-sql?

For TAB and ENTER

SELECT
    -- TRIM AND REPLACE `TAB` AND `ENTER`
    LTRIM(RTRIM(
        REPLACE(
            REPLACE(
                REPLACE(columnname, CHAR(9), ' '),
            CHAR(13), ' '),
        CHAR(10), ' ')
    ))

The ASCII code for tab is 9; you could try

update tablename set columnname = replace(columnname, char(9), '')

In the beginning of my TSql sProcs, I often put

   Declare @nl Char(2) = char(13) + char(10)
   Declare @tab Char(1) = char(9)
   etc...

Then you can use those declared variables anywhere in the rest of the proc without loss of clarity...