Case Sensitivity in Sybase DB

[VolkerBarth]

We are using Sql-Anywhere 10 DB with no case sensitivity select db_property('CaseSensitive') -> OFF

select db_property('charset') -> UTF-8

The database is case sensitive for English letters, but for German special letters (Ö, Ä, Ü) is not case senstivie.

Is there any explanation for that?

[VolkerBarth]

You say the following query would return 0, 1 for you?

select if 'Völker' = 'VÖLKER' then 1 else 0 end if,
       if 'Volker' = 'VOLKER' then 1 else 0 end if

It returns 1, 1 for me in a case-insensive database with 16.0.0.2798 and charset Windows-1252.

What does db_property('AccentSensitive') show?

[Baron]

select db_property('AccentSensitive') -> OFF

select if 'Völker' = 'VÖLKER' then 1 else 0 endif -> 0

select if 'Volker' = 'VOLKER' then 1 else 0 endif -> 1

[Baron]

what should I change then in order to have the same result as you?

select if 'Völker' = 'VÖLKER' then 1 else 0 endif -> 1

select if 'Volker' = 'VOLKER' then 1 else 0 endif -> 1

[VolkerBarth]

You would need to use the COMPARE function with fitting arguments instead of a simple comparison, see the mentioned FAQ.

Here some samples with UCA with case and accent ignored, your current UTF8BIN collation and my Windows-1252 collation (each which case ignored):

select compare('Völker', 'VÖLKER', 'UCA(case=ignore;accent=ignore)'),
       compare('Volker', 'VOLKER', 'UCA(case=ignore;accent=ignore)'),
       compare('Völker', 'VÖLKER', 'UTF8BIN(case=ignore)'),
       compare('Volker', 'VOLKER', 'UTF8BIN(case=ignore)'),
       compare('Völker', 'VÖLKER', '1252LATIN1(case=ignore)'),
       compare('Volker', 'VOLKER', '1252LATIN1(case=ignore)');

Returns 0, 0, 1, 0, 0, 0

I.e. both UCA with accent=ignore and Windows-1252 treat umlauts as equal, UFT8BIN does not (as John has already explained).

[johnsmirnios]

What collation are you using? If you are not using UCA, case-sensitivity only works for single-byte characters which, for UTF-8, is only ASCII.

-john.

[Baron]

Do you mean? select db_property('collation')? Then it is UTF8BIN.

The question how can I change those properties in DB?

[VolkerBarth]

To change the collation, you have to rebuild the database. However, you can do different comparisons (i.e. using different collations and properties) via the COMPARE function, cf. that FAQ.