如何在PostgreSQL中按字典序排序字母数字字符串

针对你提供的列数据，要实现预期的排序结果，需要先按字符串类型分组排序，再在每个组内进行自然排序。以下是具体的SQL实现：

实现代码 #

SELECT col_name
FROM your_table_name
ORDER BY
    -- 按字符串类型分组，设置排序优先级
    CASE
        WHEN col_name ~ '^[0-9]+$' THEN 1          -- 纯数字组（优先级最高）
        WHEN col_name ~ '^[0-9]+[A-Za-z]+$' THEN 2 -- 数字开头+字母组
        WHEN col_name ~ '^[A-Za-z]+$' THEN 3       -- 纯字母组
        WHEN col_name ~ '^[A-Za-z]+[0-9]+$' THEN 4 -- 字母开头+数字组
        ELSE 5                                     -- 其他类型（优先级最低）
    END ASC,
    -- 纯数字组内按数值排序
    CASE WHEN col_name ~ '^[0-9]+$' THEN col_name::BIGINT END ASC,
    -- 数字开头组：先按前缀数字排序，再按后缀字母排序
    CASE WHEN col_name ~ '^[0-9]+[A-Za-z]+$' THEN substring(col_name FROM '^[0-9]+')::BIGINT END ASC,
    CASE WHEN col_name ~ '^[0-9]+[A-Za-z]+$' THEN substring(col_name FROM '[A-Za-z]+$') END ASC,
    -- 纯字母组内按字符串排序
    CASE WHEN col_name ~ '^[A-Za-z]+$' THEN col_name END ASC,
    -- 字母开头组：先按前缀字母排序，再按后缀数字数值排序
    CASE WHEN col_name ~ '^[A-Za-z]+[0-9]+$' THEN substring(col_name FROM '^[A-Za-z]+') END ASC,
    CASE WHEN col_name ~ '^[A-Za-z]+[0-9]+$' THEN substring(col_name FROM '[0-9]+$')::BIGINT END ASC;

逻辑说明 #

1. 分组优先级：通过CASE语句给不同格式的字符串分配排序优先级，确保分组顺序符合预期（纯数字→数字开头→纯字母→字母开头）。
2. 组内排序：
   • 纯数字组：将字符串转为数值类型排序，避免字符串排序导致的'10'排在'2'前面的问题。
   • 数字开头组：先提取前缀数字转为数值排序，再提取后缀字母按字符串排序，保证1A、1ABCD、1B的顺序符合预期。
   • 纯字母组：直接按字符串自然排序。
   • 字母开头组：先提取前缀字母排序，再提取后缀数字转为数值排序，确保ABCD10排在ABCD5之后，而非字符串排序的ABCD10在ABCD2之前。

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