PostgreSQL的字母数字排序 [英] Alphanumeric sorting with PostgreSQL

问题描述

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

In the database, I have various alpha-numeric strings in the following format:

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

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

I want them to essentially be sorted by the number in front of the string and then the string name itself, but of course, characters are compared one by one and so the result of Order by name produces:

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不太熟悉，所以我不确定实现此目的的最佳方法是什么.我将不胜感激！

Honestly, I would be fine if the strings were just sorted by the number in front. I'm not too familiar with PostgreSQL, so I wasn't sure what the best way to do this would be. I'd appreciate any help!

推荐答案

理想的方法是对数据进行规范化并将列的两个组成部分拆分为两个单独的列. integer类型之一，text类型.

The ideal way would be to normalize your data and split the two components of the column into two individual columns. One of type integer, one text.

使用当前表，您可以执行以下操作:

With the current table, you can do something like demonstrated here:

WITH x(t) AS (
    VALUES
     ('10_asdaasda')
    ,('100_inkskabsjd')
    ,('11_kancaascjas')
    ,('45_aksndsialcn')
    ,('22_dsdaskjca')
    ,('100_skdnascbka')
    )
SELECT t
FROM   x
ORDER  BY (substring(t, '^[0-9]+'))::int     -- cast to integer
          ,substring(t, '[^0-9_].*$')        -- works as text

相同的 substring()表达式可用于拆分列.

The same substring() expressions can be used to split the column.

正则表达式具有一定的容错能力:

The regular expressions are somewhat fault tolerant:

• 第一个正则表达式从左侧选择最长的数字字符串，如果未找到数字，则选择NULL，因此对integer的强制转换不会出错.

• The first regex picks the longest numeric string from the left, NULL if no digits are found, so the cast to integer can't go wrong.

第二个正则表达式从第一个字符(不是数字或'_')中提取字符串的其余部分.

The second regex picks the rest of the string from the first character that is not a digit or '_'.

如果下划线清楚地用作分隔符，请 split_part() 更快:

If the underscore is unambiguous as separator anyway, split_part() is faster:

ORDER  BY (split_part(t, '_', 1)::int
          ,split_part(t, '_', 2)

您的示例答案

SELECT name
FROM   nametable
ORDER  BY (split_part(name, '_', 1)::int
          ,split_part(name, '_', 2)

这篇关于PostgreSQL的字母数字排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！