Sql Postgres：对包含数字的字符串列表进行排序

发布于02月26日

我有一个出售物品的列表，这些物品是按尺寸命名的，尺寸可以是一个范围.它们的名称通常看起来像8，10-14，10，但也可以有非数字字符97.5in或只是ABC.我试图找到一种简单而有效的方法，在与包含维度的定义表连接时，按这些维度对项目进行排序和显示:

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

并得到按维度排序的结果，顺序为8, 10, 10-14, 12, 14-20, 93.5in, ABC而不是10, 10-14, 12, 14-20, 8, 93.5, ABC.

排序中的ABC的顺序并不重要，只要它不会导致错误.我最感兴趣的是如何对8, 10, 10-14, 12, 14-20进行排序.

推荐答案

ICU collation可以配置为按数字而不是字母顺序对数字进行排序.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'

und-u只是未定义的默认排序规则，将用作您的自定义排序规则的基础.您可以使用不同的值，例如de-AT，这意味着DEutsch(德语)的区域设置为de,AusTrian变体的区域设置为AT
ka-shifted使其跳过标点符号和空格(用于解析数字的小数点除外)
kn或kn-true引入了数字排序