Stack Exchange
Compare two nvarchar columns with Unicode text in SQL server 2012
In MS SQL Server 2012 I want to compare two columns with data type nvarchar which contain Unicode text.
Following query returns nothing even thought the values are different.
DECLARE @TABLE TABLE(
    A nvarchar(100),
    B nvarchar(100)
);

INSERT INTO @TABLE VALUES (N'A²', N'A2')

SELECT *
FROM @TABLE 
WHERE A <> B;

I tried with binary collation and it works:
DECLARE @TABLE TABLE(
    A nvarchar(100),
    B nvarchar(100)
);

INSERT INTO @TABLE VALUES (N'A²', N'A2');

SELECT *
FROM @TABLE 
WHERE A COLLATE Latin1_General_BIN = B COLLATE Latin1_General_BIN;

Is there any other option or can this only be done with collation?

Afaik, you need to use a different collation as even the checksums of the values A2 and A^2 are the same

Latin1_General_BIN collation works and comparison is fine. What do you mean that I need to use a different collation?


Is there any other option or can this only be done with collation?

Yes it is, for instance HASHBYTES:
DECLARE @TABLE TABLE(A nvarchar(100),B nvarchar(100));
INSERT INTO @TABLE VALUES (N'A²', N'A2')

SELECT *
FROM @TABLE 
WHERE HASHBYTES('SHA2_256',A) <> HASHBYTES('SHA2_256',B);

LiveDemo
Output:
╔════╦════╗
║ A  ║ B  ║
╠════╬════╣
║ A² ║ A2 ║
╚════╩════╝

Anyway the collation solution is the cleanest one.

Another option is:
SELECT
  *
FROM
  @TABLE 
WHERE 
  CAST(A AS VARBINARY(MAX))<>CAST(B AS VARBINARY(MAX));

Which prints out the one line in the table
