Sql-server – How to check for Non-Ascii Characters

sql serversql-server-2008-r2t-sql

What is the best way to check if a VARCHAR field has Non-Ascii Characters?
CHAR(1) through CHAR(31) and CHAR(127) through CHAR(255).

I tried using PATINDEX and have run into the following issue.

Checking the lower range worked correctly.

SELECT *      
FROM mbrnotes      
WHERE PATINDEX('%[' + CHAR(1)+ '-' +CHAR(31)+']%',LINE_TEXT) > 0  

My data had three records with 0x1E and all three where returned.

But when I check just the upper range:

SELECT *      
FROM mbrnotes      
WHERE PATINDEX('%[' + CHAR(127)+ '-' +CHAR(255)+']%',LINE_TEXT) > 0 

It returns close to all the records in the table (table count 170737 and returned count 170735) and since my data did not have any values in this range I would think it should have returned no records.

Best Answer

  • Ranges in the pattern syntax use the sorting rules of your collation.

    Use a binary collate clause so the range is ordered by character code.

    (I also changed it to LIKE as I find that more obvious than PATINDEX > 0)

    SELECT *      
    FROM mbrnotes      
    WHERE LINE_TEXT LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%'  COLLATE Latin1_General_100_BIN2