T SQL Remove ASCII character from SELECT

Question

I'm using SQL Server 2014.

I have the below SQL statement to find unmatched records. However, it is now working correctly as the field 'dsc' in the OPENQUERY actually contains a horizontal tab (ASCII char 009) before the string values:

SELECT [E_Code]
FROM [Person] P
WHERE P.E_Code NOT IN (
        SELECT dsc
        FROM OPENQUERY(svr01, 'select "dsc" from TST.eth')
        )

How do I remove ASCII char 009 from the dsc field? I have tried LTRIM to no avail.

Thanks.

Answer 1

DECLARE @str VARCHAR(20) = CONCAT('This is a tab--> ', '<--');
SELECT @str, REPLACE(@str, CHAR(9), '');


SELECT [E_Code]
FROM [Person] P
WHERE P.E_Code NOT IN (
        SELECT REPLACE(dsc, CHAR(9), '')
        FROM OPENQUERY(svr01, 'select "dsc" from TST.eth')
    )