CODEWITHSUNDEEP
c#sql-serverunicode
Cherven
Cherven

Reputation: 1131

Why 'Weiss'='Weiß' or search in SQL Server over Unicode column

I have column in SQL Server defined as nvarchar and as a result (I think) 'Weiss'=='Weiß'. But in my c# code 'Weiss'!='Weiß'

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Tags]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](125) NOT NULL,
    [IsBlocked] [bit] NOT NULL,
    [Created] [datetimeoffset](7) NOT NULL,
    [Updated] [datetimeoffset](7) NOT NULL,

    CONSTRAINT [PK_dbo.Tags] 
    PRIMARY KEY CLUSTERED ([Id] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
              ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

In SQL Server:

SELECT *
FROM [Tags]
WHERE Value = 'Weiss Kreuz'

returns row where value is 'Weiß Kreuz'

Can someone explain how that happen and how to make comparison the same in c# and SQL Server?

Upvotes: 1

Views: 485

Answers (2)

Martin Liversage
Martin Liversage

Reputation: 106956

As you probably know that the German letter eszett (ß) in some words is used instead of double-s in German. So if you do a UNICODE comparison of the words Weiss and Weiß (meaning White) they are considered the same.

In C# you can do a UNICODE comparison (also described as a culture sensitive comparison) using String.Compare:

String.Compare("Weiss", "Weiß")

This will return 0 indicating that the two strings are equal.

Using the equals operator:

"Weiss" == "Weiß"

returns false but this operator uses a culture insensitive comparison.

SQL Server handles sorting and comparison of strings using a collation. Earlier versions of SQL Server used rules that were different compared to the rules used by Windows (and thus .NET/C#) but now the SQL Server collations have been aligned with the UNICODE rules in Windows.

This provides consistency across data types within SQL Server, and it also lets developers sort strings in their applications by using the same rules that are used by SQL Server.

Upvotes: 6

xanatos
xanatos

Reputation: 111950

It all depends on the collation and on how the comparison is done:

string str1 = "Weiss";
string str2 = "Weiß";
bool res = string.Compare(str1, str2) == 0;
bool res2 = string.Equals(str1,str2,StringComparison.CurrentCulture);

where string.Compare returns 0 if the two strings are equal.

From string.Compare msdn:

The comparison uses the current culture to obtain culture-specific information such as casing rules and the alphabetic order of individual characters

and string.Equals(string, string)

This method performs an ordinal (case-sensitive and culture-insensitive) comparison.

and string.Equals(string, string, StringComparison)

The comparisonType parameter indicates whether the comparison should use the current or invariant culture, honor or ignore the case of the two strings being compared, or use word or ordinal sort rules.

If you look around (e.g. SQL query that distinguishes between ß and ss), you will see that it's known that in many sql collations ss == ß.

In general you shouldn't reorder client-side things ordered by SQL, nor you should recompare client-side things compared by SQL, because the collation of SQL and the collation of .NET are different for small things (often because they use different versions of Unicode, or because for compatibility reasons they do things differently)

Upvotes: 3

Related Questions