Post reply

Bizarre behaviour...

  • January 13, 2014 at 2:27 pm

    #396104

    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('ž')

    --> Ž

  • January 13, 2014 at 6:57 pm

    #1679765

    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

  • January 14, 2014 at 9:44 am

    #1679969

    Thank you good sir 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply