I’m trying to sort column data in my h2 database, but it isn’t correct.
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!
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:
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: