Skip to content

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!

Advertisement

Answer

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