如何按规则排序generic_location列？regexp_substr排序异常求助

我需要对表中名为generic_location的列进行排序，该列数据格式为数字+字母+数字，例如4A1、5AW89、7AA89等。我尝试用regexp_substr拆分数据，并通过CAST(generic_location AS UNSIGNED) ASC排序，但结果不符合预期。

当前查询返回的排序结果为：

• 4A14
• 4A15
• 4AW39
• 4AW70
• 4A75
• 4AW83

我的当前查询语句如下：

SELECT gen_loc,
    regexp_substr(generic_location, '^[0-9]*') AS `A`,
    regexp_substr(generic_location, '[A-Z]+') AS `B`,
    REGEXP_REPLACE(generic_location, '([0-9]+[A-Z]+)+', '') AS `C`
FROM boms ORDER BY CAST(A AS UNSIGNED) ASC, CAST(B AS UNSIGNED), CAST(C AS UNSIGNED);

我期望的排序结果为：

• 4A14
• 4A15
• 4A75
• 4AW39
• 4AW70
• 4AW83

请问是否有方法实现该排序效果？

问题分析 #

你的排序逻辑核心问题在于对字母部分B的处理：将字母转成无符号整数完全不符合需求，字母段应该按字符串字典序排序，而非数值。同时原拆分规则的准确性也需要调整，确保各段数据提取无误。

修正后的查询方案 #

调整拆分规则后，按照「前缀数字（数值序）→ 中间字母（字符串序）→ 后缀数字（数值序）」的优先级排序即可实现预期效果：

SELECT gen_loc,
    -- 提取开头的连续数字
    REGEXP_SUBSTR(generic_location, '^[0-9]+') AS prefix_num,
    -- 提取中间的连续字母（不区分大小写的话可加i修饰符）
    REGEXP_SUBSTR(generic_location, '[A-Z]+') AS letter_part,
    -- 提取结尾的连续数字
    REGEXP_SUBSTR(generic_location, '[0-9]+$') AS suffix_num
FROM boms
ORDER BY
    CAST(prefix_num AS UNSIGNED) ASC,
    letter_part ASC,
    CAST(suffix_num AS UNSIGNED) ASC;

效果说明 #

该查询会先按前缀数字的大小排序，再按中间字母的字典序（比如A会排在AW之前），最后按后缀数字的大小排序，完全匹配你期望的排序结果。

如果使用的是MySQL 8.0及以上版本，REGEXP_SUBSTR可直接生效；若为更低版本，可通过SUBSTRING结合REGEXP_INSTR、LOCATE函数实现等价的拆分逻辑。

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