sqlpostgresql

Postgres: Sort a list of strings that contain numbers

I've got a list of items for sale that are named by their dimensions, and the dimensions can be a range. Their names generally look like 8, 10-14, 10, but can also have non-numeric characters 97.5in or just ABC. I'm trying to find an easy and efficient way to sort and display items by those dimensions when joined against the definition table that contains the dimensions:

SELECT *
FROM inventory i
    JOIN item_definition def ON i.def_id = def.id
ORDER BY def.dimension ASC

And get results ordered by dimension in the order of 8, 10, 10-14, 12, 14-20, 93.5in, ABC instead of 10, 10-14, 12, 14-20, 8, 93.5, ABC.

The order of ABC within the sort doesn't matter as long as it doesn't cause errors. I'm most interested in how to sort 8, 10, 10-14, 12, 14-20.

Solution

  • ICU collation can be configured to sort numbers numerically instead of alphabetically. Demo at db<>fiddle

    CREATE COLLATION num_ignore_punct (
 provider = icu, 
 deterministic = false, 
 locale = 'und-u-ka-shifted-kn');

create table item_definition
(dimension text collate num_ignore_punct);

insert into item_definition values
(8),(1.5),(1.4),(1),( 10), (0),('10-14'), (12),
('14-20'), ('93.5in'), ('ABC');

select * from item_definition
order by dimension COLLATE num_ignore_punct;
    dimension
    0
    1
    1.4
    1.5
    8
    10
    10-14
    12
    14-20
    93.5in
    ABC 
    locale = 'und-u-ka-shifted-kn'