I have a table having a column by name Description with NVARCHAR datatype. It may contain Unicode characters. I needed to find in which row it exists. I used this query which returns the row containing Unicode characters.
MS Access
To find Unicode characters in MS Access, I could not found a better way. so the best way is to import the access data into SQL Server and follow above method.
SELECT * FROM Mytable WHERE [Description] <> CAST([Description] as VARCHAR(1000))
This query works as well
SELECT * FROM [ITEM]
WHERE [DESC] LIKE N'%[^ -~]%' collate Latin1_General_BIN
WHERE [DESC] LIKE N'%[^ -~]%' collate Latin1_General_BIN
MS Access
To find Unicode characters in MS Access, I could not found a better way. so the best way is to import the access data into SQL Server and follow above method.
yes..thanks...your query works as expected.
ReplyDeleteAdded to display the invalid character and its ASCII code
SELECT
rowdata,
PATINDEX (N'%[^ -~' +CHAR(9) + CHAR(13) + ']%'COLLATE Latin1_General_BIN,RowData) AS [Position],
SUBSTRING(rowdata, PATINDEX (N'%[^ -~' +CHAR(9) + CHAR(13) +' ]%'COLLATE Latin1_General_BIN,RowData),1) AS [InvalidCharacter],
ASCII(SUBSTRING(RowData,PATINDEX (N'%[^ -~' +CHAR(9) + CHAR(13) +' ]%'COLLATE Latin1_General_BIN,RowData),1)) as [ASCIICode]
FROM #Temp_RowData
WHERE RowData LIKE N'%[^ -~' +CHAR(9) + CHAR(13) +']%' COLLATE Latin1_General_BIN
Can you please explain your code, I don't understand it.
Delete