如何正确排序英国邮编？ORDER BY排序结果不符合预期求助

这是英国邮编排序时非常常见的坑——数据库默认把邮编当作字符串按字符顺序比较，所以SK1之后会优先匹配第二个字符是1的SK11、SK13，而不是第二个字符是2的SK2。针对全英国范围的邮编（不管是1个字母开头、2个字母开头，还是包含内码部分），给你几个实用的解决方案：

方法1：直接在ORDER BY中拆分字母和数字部分排序 #

这是最直接的方式，不用修改表结构，适合小型报表场景。

适用于SQL Server的写法 #

SELECT Postcode
FROM YourTableName
ORDER BY
    -- 提取邮编开头的字母前缀（匹配第一个数字出现前的所有字母）
    SUBSTRING(Postcode, 1, PATINDEX('%[0-9]%', Postcode) - 1),
    -- 提取外码中的数字部分并转为整数（按数值排序）
    CAST(SUBSTRING(Postcode, PATINDEX('%[0-9]%', Postcode), CHARINDEX(' ', Postcode) - PATINDEX('%[0-9]%', Postcode)) AS INT),
    -- 最后按内码部分排序（如果需要完整排序的话）
    SUBSTRING(Postcode, CHARINDEX(' ', Postcode) + 1, LEN(Postcode))

适用于MySQL的写法 #

SELECT Postcode
FROM YourTableName
ORDER BY
    -- 提取开头的字母前缀
    REGEXP_SUBSTR(Postcode, '^[A-Za-z]+'),
    -- 提取第一个数字序列转为整数
    CAST(REGEXP_SUBSTR(Postcode, '[0-9]+', 1, 1) AS UNSIGNED),
    -- 内码部分排序
    REGEXP_SUBSTR(Postcode, '[A-Za-z]{2}$')

说明：这些语句会自动识别1个字母开头（如W1 1AA）、2个字母开头（如SK1 1AB）甚至带中间字母的邮编（如EC1A 1BB），拆分出字母前缀和数字部分后，数字按数值而非字符串顺序排序，就能得到你想要的SK1 > SK2 > SK11 > SK13的结果。

方法2：创建计算列优化排序效率 #

如果你的报表数据量较大，或者需要频繁按邮编排序，建议新增两个计算列来存储拆分后的字母前缀和数字数值，这样排序时性能会更好：

1. 添加计算列（以SQL Server为例）：

ALTER TABLE YourTableName
ADD Postcode_Letter AS SUBSTRING(Postcode, 1, PATINDEX('%[0-9]%', Postcode) - 1) PERSISTED,
    Postcode_Number AS CAST(SUBSTRING(Postcode, PATINDEX('%[0-9]%', Postcode), CHARINDEX(' ', Postcode) - PATINDEX('%[0-9]%', Postcode)) AS INT) PERSISTED

2. 排序时直接使用计算列：

SELECT Postcode
FROM YourTableName
ORDER BY Postcode_Letter, Postcode_Number, SUBSTRING(Postcode, CHARINDEX(' ', Postcode) + 1, LEN(Postcode))

特殊情况处理：邮编无空格的场景 #

如果你的邮编存储时没有标准的空格（比如SK11AB而非SK1 1AB），可以先通过字符串函数补全空格，或者调整拆分逻辑：

-- SQL Server示例：先补全空格
SELECT Postcode,
       CASE WHEN LEN(Postcode) = 6 THEN STUFF(Postcode, 4, 0, ' ')
            WHEN LEN(Postcode) = 7 THEN STUFF(Postcode, 5, 0, ' ')
            ELSE Postcode END AS FormattedPostcode
FROM YourTableName
ORDER BY
    SUBSTRING(FormattedPostcode, 1, PATINDEX('%[0-9]%', FormattedPostcode) - 1),
    CAST(SUBSTRING(FormattedPostcode, PATINDEX('%[0-9]%', FormattedPostcode), CHARINDEX(' ', FormattedPostcode) - PATINDEX('%[0-9]%', FormattedPostcode)) AS INT),
    SUBSTRING(FormattedPostcode, CHARINDEX(' ', FormattedPostcode) + 1, LEN(FormattedPostcode))

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