question

JatinSharma-9839 avatar image
0 Votes"
JatinSharma-9839 asked JatinSharma-9839 commented

German Collation comparision as Nvarchar

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

80773-image.png


sql-server-transact-sql
image.png (86.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered JatinSharma-9839 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Olaf,

Thank you for you reply.


I understand this the concept of having different ways of writing certain characters in German and comparing them. My basic problem is why using CAST in queries changes the result as both are strings values.

My database collation is already "SQL_Latin1_General_CP1_CI_AS" and its giving me correct result, but when I compare any value which is store in table is not giving me desired result.

If you use case in your last query you will get different result from what it is giving now, see below:

  • select case when CAST('Weßels' AS NVARCHAR(10)) collate SQL_Latin1_General_CP1_CI_AS = CAST('Wessels' AS NVARCHAR(10)) collate SQL_Latin1_General_CP1_CI_AS then 'Equal' else 'Unequal' end;

Thanks
Jatin

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered JatinSharma-9839 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog : thanks for your reply. You are correct the rules for comparing varchar and nvarchar is different, when I convert my column from from nvarchar to varchar it works.


Thank You
Jatin

0 Votes 0 ·