Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bizarre behaviour... Expand / Collapse
Author
Message
Posted Monday, January 13, 2014 2:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 12:07 PM
Points: 2, Visits: 4
Does this seem weird to you too?

I know that é is the same letter as É as I speak fluent french but what about this accented "Z"? How can it run through UPPER and fail the case-insensitive equality check?


--SQL_Latin1_General_CP1_CI_AI

SELECT CASE WHEN 'ž'='Ž' THEN 'TRUE' ELSE 'FALSE' END
--> FALSE

SELECT CASE WHEN 'é'='É' THEN 'TRUE' ELSE 'FALSE' END
--> TRUE

SELECT UPPER('ž')
--> Ž
Post #1530463
Posted Monday, January 13, 2014 6:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:27 AM
Points: 1,213, Visits: 1,684
Dont use SQL_Latin1_General_CP1_CI_AS / AI with a varchar variable datatype if you have unicode data. You will experience problems like the one you have due to an implicit conversion. Another example is documented here:
http://www.olcot.co.uk/sql-blogs/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as

Note if you use unicode datatypes, you will get the correct answer, TRUE;
SELECT CASE WHEN N'ž'= N'Ž' THEN 'TRUE' ELSE 'FALSE' END

The newer windows collation Latin1_CP1_CI_AS / AI will give you the correct answer as it has better rules for comparing non-unicode to unicode data.
http://support.microsoft.com/kb/322112

Post #1530514
Posted Tuesday, January 14, 2014 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 12:07 PM
Points: 2, Visits: 4
Thank you good sir :)
Post #1530756
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse