javadatabaseh2collate

H2 DB Incorrect special characters sorting

I'm trying to sort column data in my h2 database, but it isn't correct.

enter image description here

I think that change of collation may help. I tried to set collation in datasource url in many ways e.g.: spring.datasource.url = jdbc:h2:mem:testdb;COLLATION='ENGLISH'

spring.datasource.url = jdbc:h2:mem:testdb;SET COLLATION='EN''

spring.datasource.url = jdbc:h2:mem:testdb;SET COLLATION ENGLISH STRENGTH PRIMARY'

And I'm still have an syntax error like this Syntax error in SQL statement "SET COLLATION 'ENGLISH'[*]"; expected "identifier"; SQL statement: SET COLLATION 'ENGLISH' [42001-200]

Am I doing something wrong? Or there is some other way to solve my problem? Thanks in advance!

Solution

  • H2 Console uses browser's sorting capabilities provided by simple Array.sort(), they aren't affected by collation setting of H2. I created a feature request for this problem: https://github.com/h2database/h2database/issues/2694

    The URL jdbc:h2:mem:testdb;COLLATION=POLISH is correct. You can test that it works:

    SELECT * FROM
    (VALUES 'AAAAAA', 'LLLLLLLLL', 'ZZZZZZ', 'ĄĄĄĄĄĄ', 'ŁŁŁŁŁŁ') T(V)
    ORDER BY V;
> V
> --------- 
> AAAAAA
> ĄĄĄĄĄĄ
> LLLLLLLLL
> ŁŁŁŁŁŁ
> ZZZZZZ

    Without POLISH collation result will be just like on yours screenshot.

    So the oblivious workaround is to append ORDER BY BUILDINGS_NUMBER to your query.

    Note that there are different strength levels of collation. For example, for SECONDARY you can use jdbc:h2:mem:testdb;COLLATION=POLISH STRENGTH SECONDARY. See documentation for more details: https://h2database.com/html/commands.html#set_collation