I want my select queries able to do natural sort using these concepts: https://rosettacode.org/wiki/Natural_sorting
1 Answers
You can play with collation settings like in the query below.
Take into account that ClickHouse has the collation bug#7482 and fails for some languages such as en, de.
SELECT arrayJoin(['kk 50', 'KK 01', ' KK 2', ' KK 3', 'kk 1', 'x9y99', 'x9y100']) item
ORDER BY item ASC
/*
Result:
┌─item──────┐
│ KK 2 │
│ KK 3 │
│ KK 01 │
│ kk 1 │
│ kk 50 │
│ x9y100 │
│ x9y99 │
└───────────┘
*/
SELECT arrayJoin(['kk 50', 'KK 01', ' KK 2', ' KK 3', 'kk 1', 'x9y99', 'x9y100']) item
ORDER BY item ASC COLLATE 'tr-u-kn-true-ka-shifted'
/*
Result:
┌─item──────┐
│ kk 1 │
│ KK 01 │
│ KK 2 │
│ KK 3 │
│ kk 50 │
│ x9y99 │
│ x9y100 │
└───────────┘
*/
Related