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

Related Solutions

Sql-server – Small Squares or Blocks after string in table field

Some checks

Do both your Dev and Prod link to the same Navision DB?
Compare the rows in sys.servers, especially the is_collation_compatible column
Is the linked server via an ODBC/DSN or is it simple "SQL Server"? If ODBC then are settings the same in the DNS?

I suspect number 2 or 3 if the all your servers link to the same box.

SQL Server Count Data – Counting Data on a Third Table Through Link

Here's what I came up with (SQLFiddle):

SELECT
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department,
  SUM(
    CASE
      WHEN d.Type = 'Cellphone' THEN 1
      ELSE 0
    END
  ) AS [Mobile Devices],
  SUM(
    CASE
      WHEN d.Type = 'Computer' OR d.Type = 'Disc' THEN 1
      ELSE 0
    END
  ) AS [Computers],
  SUM(
    CASE
      WHEN d.Type = 'Camera' OR d.Type = 'Video Cassette' THEN 1
      ELSE 0
    END
  ) AS [Audiovisual]
FROM SupportTicket st
JOIN Link l 
  ON st.SupportTicket_ID IN (l.Entity_ID1, l.Entity_ID2)
JOIN Device d 
  ON d.Device_ID IN (l.Entity_ID1, l.Entity_ID2)
WHERE 
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted >= CONVERT(datetime, '20140201', 112)
  AND st.Date_Submitted < CONVERT(datetime, CONVERT(date, CURRENT_TIMESTAMP))
GROUP BY
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department
ORDER BY
  st.SupportTicket_ID;

The basic idea is to compute the sum of an expression that returns 1 if the current row has the required type, and zero otherwise. It's probably possible using PIVOT syntax as well.

Best Answer

Related Solutions

Sql-server – Small Squares or Blocks after string in table field

SQL Server Count Data – Counting Data on a Third Table Through Link

Related Question