Hello,
Here in Germany it's quite common to thread "ß" and the double "s" as equal, also by umlaut like "ä"="ae", "ö"="oe". And some collations respects that.
Little example
--Unicode compare => Equal
select case when N'Weßels' = N'Wessels' then 'Equal' else 'Unequal' end;
-- Ascii compare with Windows collation => Equal
select case when 'Weßels' collate Latin1_General_CI_AS = 'Wessels' collate Latin1_General_CI_AS then 'Equal' else 'Unequal' end;
-- Ascii compare with SQL collation => Unequal
select case when 'Weßels' collate SQL_Latin1_General_CP1_CI_AS = 'Wessels' collate SQL_Latin1_General_CP1_CI_AS then 'Equal' else 'Unequal' end;
If you want to compare on equal independent of code pages etc, then use a binary collation