sql - 使用 PostgreSQL 进行字母数字排序

Question

在数据库中，我有以下格式的各种字母数字字符串:

10_asdaasda
100_inkskabsjd
11_kancaascjas
45_aksndsialcn
22_dsdaskjca
100_skdnascbka

我希望它们基本上按字符串前面的数字排序，然后按字符串名称本身排序，当然，字符是一个一个比较的，因此按名称排序的结果会产生:

10_asdaasda
100_inkskabsjd
100_skdnascbka
11_kancaascjas
22_dsdaskjca
45_aksndsialcn

而不是我喜欢的顺序:

10_asdaasda
11_kancaascjas
22_dsdaskjca
45_aksndsialcn
100_inkskabsjd
100_skdnascbka

老实说，如果字符串只是按前面的数字排序就没问题了。我不太熟悉 PostgreSQL，所以我不确定最好的方法是什么。如果有任何帮助，我将不胜感激!

Answer 1

理想的方法是 normalize 您的设计并将列的两个组件拆分为两个单独的列。一种整数类型，一种文本。

使用当前表，您可以:

SELECT col
FROM   tbl
ORDER  BY (substring(col, '^[0-9]+'))::int  -- cast to integer
         , substring(col, '[^0-9_].*$');    -- works as text

同样的substring() expressions可以用来拆分列。

这些正则表达式具有一定的容错性:
第一个正则表达式从左边选择最长的数字字符串，如果没有找到数字，则为 NULL，因此转换为 integer 不会出错。
第二个正则表达式从第一个不是数字或“_”的字符中挑选字符串的其余部分。

如果下划线 (_) 是明确的分隔符，split_part() 更快:

SELECT col
FROM   tbl
ORDER  BY split_part(col, '_', 1)::int
        , split_part(col, '_', 2);

db<>fiddle here

参见:

• Split comma separated column data into additional columns