|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
RUE._What's_wrong?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/ Show quote "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 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: Show quote > > 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 After searching on Google for a little while, I have found the> Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS ? 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  |
|||||||||||||||||||||||
Other interesting topics