Very strange sql 2017 behavior Extended Ascii

1

I needed a function to determine if a string had any ascii char that wasn't between 1 and 126 in it

We relied on this function previously, which is relatively popular online, which would look at all strings whether unicode or other and determine if it was an english string containing only char(1) - char(126), but it appears it does not return proper results.

select case 
when not Cast(@message  as varchar(4000)) = @message then 1
when @message LIKE '%[' + CHAR(127) + '-' +  CHAR(255) + ']%' COLLATE Latin1_General_100_BIN2  then 1
else 0 end
end

We went under the assumption that it was working, but then found a very strange issue. is ascii char 0146 (A single quote from the extended ascii was not returning properly, note that is not the single quote from standard ascii, it's char 0146)

declare @message nvarchar(100) = N'Here is a extended quote ’ this will not work'

   select case 
    when not Cast(@message  as varchar(4000)) = @message then 1
    when @message LIKE '%[' + CHAR(127) + '-' +  CHAR(255) + ']%' COLLATE Latin1_General_100_BIN2  then 1
    else 0 end
    end

As you can see this is returning 0 even though char 146 exists in the string. After some trial and error I found if I separated up to 155 and after it then worked? Why, shouldn't this find the whole range? Here is the working version, but I want to more figure out why... It is my assumption using the like [ x - X] function is not looking at those characters in order, which would also mean my fix solution can potentially not be a 100% accurate solution until I know that the range I specify is at the start of char 127 and ends at 255.

select case
     when not Cast(@message  as varchar(4000)) = @message then 1
     when @message LIKE '%[' + CHAR(127) + '-' +  CHAR(155) + ']%' COLLATE Latin1_General_100_BIN2  then 1
     when @message LIKE '%[' + CHAR(156) + '-' +  CHAR(255) + ']%' COLLATE Latin1_General_100_BIN2  then 1
     else 0 end
     end
2

I am not familiar with the collation you mentioned and using that to eliminate characters.

The following is not a solution, but to understand why the behavior is not what you expect:

DECLARE @message VARCHAR(100) = N'Here is a extended quote ’ this will not work';

SELECT 
    CASE
        WHEN NOT CAST(@message AS VARCHAR(4000)) = @message THEN 1
        WHEN @message LIKE CAST('%[' + CHAR(127) + '-' + CHAR(255) + ']%' AS VARCHAR(MAX)) COLLATE Latin1_General_100_BIN2 THEN 1
        ELSE 0
    END;

That should return 1. Run the following and you will see that you are ending up with 8217 for unicode

DECLARE @x NCHAR(1) = N'’';
SELECT ASCII(@x)
SELECT CAST(@x AS CHAR(1))

SELECT UNICODE(@x);
SELECT NCHAR(8217);

SELECT CAST(@x AS VARBINARY(MAX))