Is there a way for sorting numbers with leading zeros like strings otherwise like numbers?-CodePudding

I have a collation for natural sorting strings (provider = icu, locale = 'en-u-kn-true') in postgres 14.1 database. NodeJS v12.16.1. When strings have non-zero leading numbers like 1 2 11 22, sortation is working fine for me. If the numbers start with zero, I get the correct order in terms of numbers [000, 00, 0, 001, 01, 1, 002, 02, 2], but in this case I expect an order like [000, 001, 002, 00, 01, 02, 0, 1, 2]. Is there any way to get this behavior using additional settings for locale or some other way. The main thing is that it should work fast (up to 2-3 seconds), for about one million records.

CodePudding user response：

You can sort by the nomber of leading zeros, then by the numeric value:

ORDER BY
   length(col)
      - length(trim(LEADING '0' FROM col))
      DESC,
   col COLLATE natural_coll