PostgreSQL查询：字母数字字符串排序顺序错误问题

问题分析 #

你的原查询仅针对以数字开头的字符串处理排序逻辑，对于DR-xxx这类以文本开头的字符串，排序时直接使用原始字符串的字典序，导致DR-1001排在DR-101之前（字符串字典序中1001 < 101）；而对于MY-Prefix-x-Entrance-y这类多段字母数字混合的字符串，原查询会将所有数字合并为单个整数（比如MY-Prefix-1-Entrance-1提取出数字11，MY-Prefix-10-Entrance-1提取出101），完全不符合按分段数字排序的需求。

我们需要实现自然排序：将字符串拆分为交替的文本段和数字段，数字段按数值大小排序，文本段按字典序排序，依次比较每个分段。

解决方案 #

使用PostgreSQL的正则拆分函数，将字符串拆分为文本和数字片段，分别转换类型后按数组排序：

SELECT title
FROM door
ORDER BY
  ARRAY(
    SELECT CASE
             WHEN segment ~ '^\d+$' THEN segment::BIGINT  -- 数字段转数值类型
             ELSE segment                                -- 文本段保留原类型
           END
    FROM regexp_split_to_table(title, '(\d+)') AS segment
    WHERE segment != ''  -- 过滤空片段
  );

代码解释 #

1. regexp_split_to_table(title, '(\d+)')：将字符串按数字段拆分，同时保留数字段和非数字段的原始顺序（括号捕获分隔符，因此会返回数字段内容）。例如MY-Prefix-1-Entrance-1会被拆分为MY-Prefix-、1、-Entrance-、1。
2. CASE语句：判断每个片段是否为纯数字，是则转换为BIGINT类型（确保大数字也能正确排序），否则保留文本类型。
3. 数组排序：PostgreSQL会按数组中元素的顺序依次比较，数字按数值大小、文本按字典序排序，最终实现自然排序效果。

验证结果 #

执行上述查询后，你的数据会按预期排序：

"DR-1"
"DR-01"
"DR-02"
"DR-03"
"DR-04"
"DR-100"
"DR-101"
"DR-102"
"DR-104"
"DR-1001"
"Entrance-1"
"Entrance-2"
"MY-Prefix-1-Entrance-1"
"MY-Prefix-2-Entrance-1"
"MY-Prefix-3-Entrance-1"
"MY-Prefix-8-Entrance-1"
"MY-Prefix-9-Entrance-1"
"MY-Prefix-9-Entrance-2"
"MY-Prefix-9-Entrance-3"
"MY-Prefix-10-Entrance-1"
"MY-Prefix-11-Entrance-1"
"MY-Prefix-19-Entrance-1"
"MY-Prefix-20-Entrance-1"

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