In Postgres I need to sort text with natural order, but with one exception – if the string has only number, it should be placed at top.
So I need such order:
["98", "125", "134", "148", "265", "634", "1233", "5231", "1m1ds", "1m2", "1m3", "1n3", "1w3r", "2m3", "2n3ds", "9t6","12gh", "13jy","25hg", "123y", "des2", "nme", "wer5"]
I tried with this:
CREATE COLLATION IF NOT EXISTS numeric (provider = icu, locale = 'en@colNumeric=yes');
ALTER TABLE "baggage_belts" ALTER COLUMN "name" type TEXT COLLATE numeric;
and it is ok, but numbers are mixed into numbers+text:
[1m1ds, 1m2, 1m3, 1n3, 1w3r, 2m3, 2n3ds, 9t6, 12gh, 13jy, 25hg, 98, 123y, 125, 134, 148, 265, 634, 1233, 5231, des2, nme, wer5]
Anyone has knowledge is it possible make it works with "empty" numbers first?
>Solution :
Then you should add a second ORDER BY expression that checks for non-digits:
ORDER BY name ~ '[^[:digit:]]', name
This relies on FALSE < TRUE, and you can support it with a two-column index defined like the ORDER BY clause.