CODEWITHSUNDEEP
sqlsql-servert-sql
mohamed-mhiri
mohamed-mhiri

Reputation: 232

Why two strings may compare as equal?

I am working on string comparision with = and LIKE and I found this in the documentation :

Depending on the collating sequence, two strings may compare as equal even if they are of different lengths or contain different sequences of characters. When the operations MAX, MIN, DISTINCT, references to a grouping column, and the UNION, EXCEPT, and INTERSECT operators refer to character strings, the specific value selected by these operations from a set of such equal values is implementation-dependent.

Upvotes: 1

Views: 358

Answers (2)

Thom A
Thom A

Reputation: 95554

Collations can be sensitive to different things. Currently, there are Case, Accent, Kana, Width and supplementary character sensitivities. There are also Binary collations as well.

So for a collation that is both Case and Accent insensitive, then the strings N'ABCDEFG' and N'äbçdéfg' would be seen as the "same" string despite that they share no same literal characters.

Emoticons are another area where characters that are completely different can be seen as the same. For example the below returns 1, despite that one string contains emoticons of a cat and a dragon and the other a cake:

SELECT CASE WHEN N'🐱🐉' COLLATE SQL_Latin1_General_CP1_CI_AS = N'🎂' COLLATE SQL_Latin1_General_CP1_CI_AS  THEN 1 END;

Upvotes: 2

Heinzi
Heinzi

Reputation: 172220

You are asking for a concrete example? In German, we have the lower-case letter ß (length 1). In upper-case, this letter is usually written as SS (length 2) instead. Thus, using a case-insensitive Western-European collation, the following SQL

SELECT CASE WHEN 'ß' = 'SS' THEN 'Yes' ELSE 'No' END

will output Yes, even though the strings have different lengths.

Upvotes: 7

Related Questions