Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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?

asked 16 Aug '19, 05:14

Baron's gravatar image

accept rate: 48%

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 and charset Windows-1252.

What does db_property('AccentSensitive') show?

(16 Aug '19, 06:44) Volker Barth
Replies hidden

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

(16 Aug '19, 06:57) 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

(20 Aug '19, 04:56) Baron

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).

(20 Aug '19, 05:59) Volker Barth

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.


permanent link

answered 16 Aug '19, 06:26

John%20Smirnios's gravatar image

John Smirnios
accept rate: 37%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 16 Aug '19, 05:14

question was seen: 1,158 times

last updated: 20 Aug '19, 07:07