CODEWITHSUNDEEP
sql-server
wormdiddy
wormdiddy

Reputation: 21

SQL Server interpreting 2 Unicode characters as the same

I am using SQL Server 2019 at the moment but would like this to work in all versions, and need to store strings with different unicode values. One string has 0xC3 0x89, and the other has 0x45 0xCC 0x81. The rest of the characters are the same.

testÉ
testÉ

I am using Latin1_General_100_CI_AS_SC_UTF8 collation, column is of datatype nvarchar, and I am prefixing the strings with N.

Can anyone tell me how to insert these so that SQL Server stores them as different?

INSERT INTO directory (directory_id, full_path) 
VALUES (1, N'testÉ');

INSERT INTO directory (directory_id, full_path) 
VALUES (2, N'testÉ');

When I try to select one or the other:

SELECT directory_id, full_path 
FROM directory 
WHERE full_path = N'testÉ'

SELECT directory_id, full_path 
FROM directory 
WHERE full_path = N'testÉ'

I end up getting both.

Upvotes: 0

Views: 151

Answers (1)

wormdiddy
wormdiddy

Reputation: 21

It looks like Latin1_general_100_BIN2 is working the way I would expect.

Upvotes: 2

Related Questions