Hello,

in a case-function the therm 'ß' = 'ss' evaluates to true. Is there a chance 
to fix the problen in a particular query or better to fix it generally in SQL 
Server?

Thanks in advance,
  Klaus

SQL Server Version: 8.00.760 (SP3)
Language: german
Collation: Latin1_General_CI_AS

Sample SQL statement to reproduce the behavior:

select case when 'ss' = 'ß' then 'ss = ß!' else 'ok' end as 'Test ss=ß',
       case when 'ss' = 's' then 'ss = s!' else 'ok' end as 'Test ss=s',
       case when 'ae' = 'ä' then 'ae = ä!' else 'ok' end as 'Test ae=ä',
       case when 'oe' = 'ö' then 'oe = ö!' else 'ok' end as 'Test oe=ö',
       case when 'ue' = 'ü' then 'ue = ü!' else 'ok' end as 'Test ue=ü'

Resultset in our environment:
Test ss=ß Test ss=s Test ae=ä Test oe=ö Test ue=ü 
--------- --------- --------- --------- --------- 
ss = ß!   ok        ok        ok        ok

Hi

DIN, SQL ANSI-92 and ISO standards all dictate that the "sharp s" evalutes 
to "ss". It is nothing you can change in the DB. SQL Server is just following 
the standards.

Regards 
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/



"Klaus" wrote:

> Hello,
> 
> in a case-function the therm 'ß' = 'ss' evaluates to true. Is there a chance 
> to fix the problen in a particular query or better to fix it generally in SQL 
> Server?
> 
> Thanks in advance,
>   Klaus
> 
> SQL Server Version: 8.00.760 (SP3)
> Language: german
> Collation: Latin1_General_CI_AS
> 
> Sample SQL statement to reproduce the behavior:
> 
> select case when 'ss' = 'ß' then 'ss = ß!' else 'ok' end as 'Test ss=ß',
>        case when 'ss' = 's' then 'ss = s!' else 'ok' end as 'Test ss=s',
>        case when 'ae' = 'ä' then 'ae = ä!' else 'ok' end as 'Test ae=ä',
>        case when 'oe' = 'ö' then 'oe = ö!' else 'ok' end as 'Test oe=ö',
>        case when 'ue' = 'ü' then 'ue = ü!' else 'ok' end as 'Test ue=ü'
> 
> Resultset in our environment:
> Test ss=ß Test ss=s Test ae=ä Test oe=ö Test ue=ü 
> --------- --------- --------- --------- --------- 
> ss = ß!   ok        ok        ok        ok
>

Hello, Klaus

You can force that comparison to use another collation, like
Latin1_General_BIN or SQL_Latin1_General_CP1_CI_AS:

select case when 'ss' COLLATE Latin1_General_BIN = 'ß'
then 'ss = ß!' else 'ok' end as 'Test ss=ß'

Razvan

Can you try using the Latin1_General_BIN collation?


ML

Hi,

if we compare with Latin1_General_BIN collation the actual problem is solved.

The best way to fix our problem sems to be to change the collation for the 
whole server or the whole database. Unfortunately for this task we can not 
use Latin1_General_BIN because the sort results are not satisfactory.

Is there a better soting collation which makes a difference between 'ß' and 
'ss'?

Thanks,
  Klaus

Hello, Klaus

Have you tried the SQL_Latin1_General_CP1_CI_AS collation ?
(I mentioned it in the previous message)

Razvan

Sorry, I've only tested Latin1_General_BIN - shame on me!

Yes, SQL_Latin1_General_CP1_CI_AS also evaluates the difference between 'ss' 
and 'ß' but sorts in th first tests the same way like Latin1_General_CI_AS.

Is there a documentation of the collations to see the differences between 
Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS and if so, where can I 
get it?

Klaus,

Just create a table, insert all values you like to see sorted, and use
the COLLATE keyword in the ORDER BY clause.

Note that you are not required to change the column collation in the
database. You can also use the COLLATE keyword for any predicate and
ORDER expression. However, in those cases indexes cannot be fully used.

So you could write something like

  WHERE Test = 'ß' COLLATE Latin1_General_BIN

but still keep the column collation on Latin1_General_CI_AS, so any
ordering will be unchanged.

HTH,
Gert-Jan


Klaus wrote:
> 
> Sorry, I've only tested Latin1_General_BIN - shame on me!
> 
> Yes, SQL_Latin1_General_CP1_CI_AS also evaluates the difference between 'ss'
> and 'ß' but sorts in th first tests the same way like Latin1_General_CI_AS.
> 
> Is there a documentation of the collations to see the differences between
> Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS and if so, where can I
> get it?

> Is there a documentation of the collations to see the differences between
> Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS ?

After searching on Google for a little while, I have found the
following pages that may help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;322112
http://msdn.microsoft.com/library/en-us/architec/8_ar_da_1pwz.asp
http://msdn.microsoft.com/library/techart/IntlFeaturesInSQLServer2000.htm
http://www.dbazine.com/db2/db2-disarticles/gulutzan1a
http://developer.mimer.com/collations/charts/

These articles do not provide a direct, precise answer to your
question, but may help you better understand how different collations
work.

Razvan