Strange order varchar column

[Helmut Leininger]

Hi,

I have a column defined as SORTNAME VARCHAR(70)
Rows contain these Values:
"LUTZ, JOSEF"
"LUTZ, JOSEF ANTON"
"LUTZ, JOSEFA"

When I do:

SELECT * from table ORDER BY SORTNAME

I get this order:

"LUTZ, JOSEF"

"LUTZ, JOSEFA"

"LUTZ, JOSEF ANTON"

I would expect "JOSEF ANTON" being before "JOSEFA". What is going wrong?
Note:
changing the definition to CHARACTER(70) does not change the behaviour.

Thanks for your help.
Helmut Leninger

[Evgenij Ryazanov]

Hello.

The default sort order when database collation wasn't specified or was specified as OFF is

LUTZ, JOSEF
LUTZ, JOSEF ANTON
LUTZ, JOSEFA

because this is how java.lang.String.compareTo() works.

If database collation was specified, for example, as EN, the sort order is

LUTZ, JOSEF
LUTZ, JOSEFA
LUTZ, JOSEF ANTON

because this is how collators (java.text.Collator) work and strength setting doesn't affect this order.

H2 doesn't provide possibility to add own rules to collators, so this behavior cannot be currently changed.

H2 also supports collators from third-party ICU4J library, but I never used it and I don't know how these collators work.

Don't try to use CHAR / CHARACTER / etc. for variable-length strings, these data types can only create additional problems, they are suitable only for fixed-length strings.

[Helmut Leininger]

Thanks for the explanation

Helmut

[Helmut Leininger]

Hi,

just FYI: ICU4J seems to do what I want (COLLATION set to GERMAN). Just added it to the classpath.

Regards
Helmut