Strange order varchar column

27 views
Skip to first unread message

Helmut Leininger

unread,
Jun 18, 2022, 12:27:44 PMJun 18
to H2 Database
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

unread,
Jun 18, 2022, 9:05:29 PMJun 18
to H2 Database
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

unread,
Jun 19, 2022, 2:50:42 AMJun 19
to H2 Database
Thanks for the explanation

Helmut

Helmut Leininger

unread,
Jun 19, 2022, 4:00:33 AMJun 19
to H2 Database
Hi,
just FYI: ICU4J seems to do what I want (COLLATION set to GERMAN). Just added it to the classpath.

Regards
Helmut
Reply all
Reply to author
Forward
0 new messages