如何对字母数字值进行自然排序？PostgreSQL技术方案问询

要实现你需要的自然排序规则，可以通过拆分字符串的非数字前缀和数字部分，分别按文本和数值排序，SQL语句如下：

SELECT alphanum
FROM alphanumeric
ORDER BY 
  -- 提取字符串中的非数字部分，纯数字的记录此部分为空，会排在最前
  regexp_replace(alphanum, '\d+', '', 'g'),
  -- 提取字符串中的数字部分并转为整数，按数值大小排序
  (regexp_replace(alphanum, '\D+', '', 'g'))::integer;

原理说明 #

默认的ORDER BY alphanum采用字典序排序，会逐个字符比较ASCII码值，因此出现'11'在'2'前、'A11'在'A2'前的不符合预期的结果——因为字符'1'的ASCII码小于'2'，字典序优先比较第一个不同的字符。

而上述SQL通过两个排序条件实现自然排序：

1. 先按非数字前缀排序：纯数字记录的前缀为空字符串，会排在所有带字母前缀的记录之前；字母前缀相同的记录会归为一组。
2. 再按数字部分的数值大小排序：把提取出的数字字符串转为整数，按数值而非字典序排序，确保'2'在'11'前、'A2'在'A11'前。

执行结果 #

执行上述SQL后，将得到你预期的排序结果：

1
2
11
111
A1
A2
A11
AB1
AB2
AB11
C3
C4
C33

内容的提问来源于stack exchange，提问作者Joe