如何按街道名称、门牌号排序同一列中的地址数据？

要实现先按街道名称字母排序、再按门牌号数字排序的需求，你需要把address列拆分为街道名称和门牌号两个部分，分别用于排序：

SELECT * 
FROM data.address
ORDER BY 
  -- 提取街道名称：去掉开头的数字和空格部分
  TRIM(SUBSTRING(address, LOCATE(' ', address) + 1)),
  -- 提取门牌号并转为整数排序
  CONVERT(SUBSTRING_INDEX(address, ' ', 1), UNSIGNED INTEGER);

语句说明 #

• TRIM(SUBSTRING(address, LOCATE(' ', address) + 1))：通过LOCATE找到第一个空格的位置，截取空格之后的部分并去除多余空格，得到完整的街道名称（比如从27 Longshire Cir中提取Longshire Cir），按这个字段的字母顺序排序。
• CONVERT(SUBSTRING_INDEX(address, ' ', 1), UNSIGNED INTEGER)：用SUBSTRING_INDEX提取第一个空格前的内容，转为无符号整数，实现门牌号的数字排序（而非字符串排序）。

如果地址存在开头多空格、门牌号带后缀（如27A）等特殊情况，可针对性调整提取逻辑，但针对你给出的示例格式，以上语句完全适用。

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