0

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!

  • OK, when I changed url to ```spring.datasource.url = jdbc:h2:mem:testdb;COLLATION=POLISH``` app is working, but sorting is still incorrect –  Jun 05 '20 at 16:31

2 Answers2

0

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

Evgenij Ryazanov
  • 6,960
  • 2
  • 10
  • 18
  • Working, thanks! Hmm, can you tell me what can I do to fix sorting like this? ```SELECT * FROM (VALUES '9B', '9A', '99A', '10') T(V) ORDER BY V DESC; > V > --------- > 9B > 9A > 99A > 10 ``` I should have 99A > 10 > 9B > 9A –  Jun 06 '20 at 14:02
  • 1
    No, you shouldn't. Character strings aren't sorted like numbers. If `V` always starts with a digit and number of digits isn't too large, try `ORDER BY CAST(REGEXP_REPLACE(V, '[^0-9].*', '') AS INTEGER) DESC, V DESC` instead. – Evgenij Ryazanov Jun 06 '20 at 14:50
  • Its working in some examples, but when i have query like this ```SELECT * FROM (VALUES '9B', '9A', '99A', '10', 'AAAA') T(V) ORDER BY CAST(REGEXP_REPLACE(V, '[^0-9].*', '') AS INTEGER) DESC``` i have a data conversion error –  Jun 06 '20 at 16:52
  • 1
    Use `SELECT * FROM (VALUES '9B', '9A', '99A', '10', 'AAAA') T(V) ORDER BY CAST(NULLIF(REGEXP_REPLACE(V, '[^0-9].*', ''), '') AS INTEGER) DESC NULLS FIRST, V DESC`; don't forget about `V DESC` to sort 9B and 9A in predictable order. You may want to use `NULLS LAST` instead of `NULLS FIRST` depending on desired order of values without numbers. If you have very large numbers, use some larger data type instead of `INTEGER`, such as `BIGINT` or even `NUMERIC(100)` etc. – Evgenij Ryazanov Jun 07 '20 at 02:52
0

Thanks very much for bringing that in. Had similar problem with my Quarkus application hooked to H2 database. Adding:

quarkus.datasource.jdbc.url=jdbc:h2:mem:testdb;COLLATION=POLISH

at my application.properties file solves the problem and all ORDER BY queries returning expected results.

Marek Martofel
  • 1