|
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? |
|
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. Do you mean? select db_property('collation')? Then it is UTF8BIN. The question how can I change those properties in DB?
(16 Aug '19, 06:35)
Baron
Replies hidden
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.
(16 Aug '19, 06:56)
Volker Barth
|
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 ifIt 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?
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
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
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).