Hi, you use wrong collation
UNICODE_CI is truly case-insensitive. In a search for e.g. 'Apple', it will also find 'apple', 'APPLE' and 'aPPLe'. UNICODE_CI_AI is accent-insensitive as well. According to this collation, 'APPEL' equals 'Appèl'. as you can see UNICODE_CI_AI is accent-insensitive use "UNICODE_CI" instead regards, Karol Bieniaszewski W dniu 2016-06-15 17:28:34 użytkownik Stefan Heymann li...@stefanheymann.de [firebird-support] <firebird-support@yahoogroups.com> napisał: > I expect that an accent insensitive compare treats accented characters > as the "same" as their un-accented counterparts because the accent > does not change the character itself but things like pronounciation or > stress. > > So in Frech, à is similar to a, é is similar to è and you use an > accent insensitive compare to find Gérard even though your search term > says Gerard (without the accent). > > However, in the German language, the letters Ö and O are two different > characters with a completely different pronounciation (the same is > true for A/Ä and U/Ü). As they look similar, the sorting is done so > that they stay together, but they can _not_ be treated as accented > versions of each other. > > When I use the UNICODE_CI_AI collation to compare them, Firebird > treats them as the same: > > select case when 'a' = 'ä' collate unicode_ci_ai then 'equal' else 'not > equal' end || ' expected: not equal' from rdb$database > union all > select case when 'O' = 'Ö' collate unicode_ci_ai then 'equal' else 'not > equal' end || ' expected: not equal' from rdb$database > union all > select case when 'Ä' = 'ä' collate unicode_ci then 'equal' else 'not equal' > end || ' expected: equal' from rdb$database > union all > select case when 'a' = 'à' collate unicode_ci_ai then 'equal' else 'not > equal' end || ' expected: equal' from rdb$database > union all > select case when 'c' = 'ç' collate unicode_ci_ai then 'equal' else 'not > equal' end || ' expected: equal' from rdb$database > union all > select case when 'é' = 'è' collate unicode_ci_ai then 'equal' else 'not > equal' end || ' expected: equal' from rdb$database > > delivers: > > equal expected: not equal > equal expected: not equal > equal expected: equal > equal expected: equal > equal expected: equal > equal expected: equal > > > Is there something that can be done to improve this? > > > Regards > > Stefan > > -- > Stefan Heymann, Tübingen, Germany > >