CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012collation
Hemus San
Hemus San

Reputation: 399

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?

Upvotes: 1

Views: 8457

Answers (2)

TT.
TT.

Reputation: 16137

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

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.

Upvotes: 1

Related Questions