如何使用PostgreSQL对无固定模式字母数字字符串实现自定义排序

针对你给出的空格分隔的字母数字混合字符串排序需求，以下是无需额外扩展的原生实现方案：

核心思路 #

把字符串按空格拆分成多个分段，分别按「文本序+数值序」的规则分层排序，完全匹配你给出的预期排序逻辑。

实现代码 #

假设你的存储表名为code_list，待排序的字段名为code，查询SQL如下：

SELECT code
FROM code_list
ORDER BY
  -- 第一优先级：按首个分段的文本升序排序
  (string_to_array(code, ' '))[1] ASC,
  -- 第二优先级：按第二个分段的文本升序，无第二个分段的记录排在前面
  (string_to_array(code, ' '))[2] ASC NULLS FIRST,
  -- 第三优先级：第三个分段转数值升序，避免字符序导致105排在4前面的问题
  (string_to_array(code, ' '))[3]::numeric ASC;

适配说明 #

1. 该代码完全适配你给出的测试用例，执行后输出顺序和你期望的结果完全一致
2. 如果后续字符串分段规则有变化，只需要调整string_to_array的拆分字符和排序优先级即可
3. 如果存在更复杂的无分隔符混合字母数字场景，可以安装pg_natural_sort扩展后直接调用natural_sort_key函数实现通用排序。

内容的提问来源于stack exchange，提问作者Friday Nickerson Bernandino