0

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.

Michael
  • 2,507
  • 8
  • 35
  • 71
  • you can use substring. – Cetin Basoz Dec 17 '19 at 13:49
  • Does this answer your question? [How do I remove extended ASCII characters from a string in T-SQL?](https://stackoverflow.com/questions/15259622/how-do-i-remove-extended-ascii-characters-from-a-string-in-t-sql) – Amira Bedhiafi Dec 17 '19 at 14:02
  • @xXx sorry no. I created the function and can see it but when I try to run it I just receive: 'RemoveNonASCII' is not a recognized built-in function name. – Michael Dec 17 '19 at 16:32

2 Answers2

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')
    )
lptr
  • 1
  • 2
  • 6
  • 16
0

Alter max recursion and create the following

    Create Function dbo.ASCIICleaner(@inputstring nvarchar(max))
    Returns nvarchar(max)
    BEGIN
    Declare @returnValue nvarchar(max) = '';
    with lengths as 
    (
        select @inputstring data, len(@inputstring) length,1 start
    )

    ,recurv as 
    (
        select data,length,UNICODE(substring(data,start,1)) chari ,start from lengths where start= 1
        union all
        select  data,length,UNICODE(substring(data,start+1,1)),start+1 from recurv where length > start
    )

    select @returnValue+= char(chari) 
    from recurv 
    where chari between 32 and 127
    order by start asc

    return @returnValue

    END


    select dbo.ASCIICleaner('aËbËcËDËEËF')

abcDEF will be returned

Alex Valentine
  • 1
  • little explanation of your code will be very helpful – 32cupo Apr 14 '20 at 12:20
  • I'll admit it could have been cleaner, By using a recursive cte you can identify characters that you want to exclude, in this case anything that is not extended character set, the only limitation is the maximum size of a recursive cte is 32767. – Alex Valentine Apr 14 '20 at 21:33
  • Meant to say, anything that is extended char set will be removed. – Alex Valentine Apr 14 '20 at 22:11