Question

SQL Server

6. Our new system cannot handle Unicode (Links to an external site.) characters, only ASCII (Links to an external site.) characters.
The current Productname data type is NVARCHAR, which is unicode, and might have non-ASCII characters in it. We need to check!
Show the ProductID, and ProductName, and the first location of any non-Unicode characters. See the note at the bottom for how to find non-Unicode characters.

Answer 1

As you mention in the question see the note at the bottom for how to find non-Unicode characters, but there is no note mentioned in the question. (As given question is not complete)

There are several ways to find non-Unicode characters from the given table by using PATINDEX or using binary collate clause so the range is ordered by character code.

Query to find the ProductName which has non-Unicode characters :

THIS QUERY WILL RETURN ALL THE NON-UNICODE RECORDS OF LOWER RANGE. (PATINDEX)

SELECT ProductID, ProductName FROM table_name WHERE PATINDEX('%[' + CHAR(1)+ '-' +CHAR(31)+']%',LINE_TEXT) > 0

-------------------------------------------------------------------------------------------------------------------------------------------

THIS QUERY WILL RETURN ALL THE NON-UNICODE RECORDS OF UPPER RANGE. (PATINDEX)

SELECT ProductID, ProductName FROM table_name WHERE PATINDEX('%[' + CHAR(127)+ '-' +CHAR(255)+']%',LINE_TEXT) > 0

------------------------------------------------------------------------------------------------------------------------------------------

THIS QUERY WILL RETURN ALL THE NON-UNICODE RECORDS OF LOWER RANGE. (LIKE)

SELECT ProductID, ProductName FROM table_name WHERE LINE_TEXT LIKE '%[' + CHAR(1)+ '-' +CHAR(31)+']%' COLLATE Latin1_General_100_BIN2

-------------------------------------------------------------------------------------------------------------------------------------------

THIS QUERY WILL RETURN ALL THE NON-UNICODE RECORDS OF UPPER RANGE. (LIKE)

SELECT ProductID, ProductName FROM table_name WHERE LINE_TEXT LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_100_BIN2

-------------------------------------------------------------------------------------------------------------------------------------------

PATINDEX gives faster solution than LIKE.