German Collation comparision as Nvarchar

Question

I'm facing an issue while comparing two strings, one with Germany special character 'ß' and other is with normal character 'ss'. My Database is "SQL_Latin1_General_CP1_CI_AS" collation which should compare these characters and it is doing as expected only when I compare them as hardcoded string. However when I use cast to convert them into NVARCHAR then it treat them equal. I don't understand why changing the type should impact. Below is my example with attach screenshot.

SELECT name, collation_name
FROM sys.databases
WHERE name = N'DBName';

/**********************************************/

IF CAST('Steds.Weßels' AS NVARCHAR(100)) = CAST('Steds.Wessels' AS NVARCHAR(100))
BEGIN
SELECT 'Equal'
END
ELSE
BEGIN
SELECT 'Not equal'
END

/**********************************************/
IF 'Steds.Weßels' = 'Steds.Wessels'
BEGIN
SELECT 'Equal'
END
ELSE
BEGIN
SELECT 'Not equal'
END

Answer 1

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

Answer 2

If you have a Windows collation, the rules for character comparison etc will be the same, no matter you have varchar or nvarchar. As a matter of fact, all operations on varchar are carried out internally by converting to UTF-16 to use the same library routines as for nvarchar.

But in an SQL collation it is different. The rules for varchar and nvarchar can be different, because completely different libraries for the comparison. The particular SQL_Latin1_General_CP1_CI_AS you are using, certainly has some rough edges. It does not understand that ss = ß. Even more fun is that it considers š and Š to be different, although the collation is touted as case-insensitive.