在MYSQL中按列中第一个字母的字母顺序排序

Question

MYSQL数据库中的一张表包含地址详细信息，例如：add1、add2、add3、district、postalTown、country。

通常按照邮政城镇进行排序是可以的，但是有些详细信息在postalTown列中包含数字，例如1420 Territet或3100 Overijse。这意味着这些详细信息将出现在Aberdeen或Bristol之上。是否有一种方法可以按照postalTown的第一个字母字符进行排序？这将意味着上述顺序为：Aberdeen，Bristol，Overijse，Territet。

谢谢

- iagdotme

---

我认为实现这一目标的最佳方法是确保postalTown中没有数字。您是否熟悉数据库规范化形式？ - zneak

我更喜欢在邮政城镇中没有数字，但这是数据库必须的方式。我可以创建一个不带数字的邮政城镇列，但我宁愿不这样做。我真的不理解在邮政城镇之前有数字的地址。 - iagdotme

告诉别人重新设计他们的数据库比提供直接解决方案更容易，但是你知道重新设计并不总是可行的。也许这是一个具有太多依赖系统的遗留系统。也许这只是一个没有整改预算的小型维护工作。 - FrustratedWithFormsDesigner

Answer 1

编写一个表达式，返回第一个字母字符，然后按照该表达式进行排序。

  Order By substring(LTrim(
           Replace(Replace(Replace(Replace(Replace(
           Replace(Replace(Replace(Replace(Replace(
             colname, '1', ''),'2',''),'3',''),'4,''),'5', ''),
                      '6',''),'7',''),'8',''),'9',''),'0',''))
                   1,1)

如果您希望按整个城市名称排序而不仅仅是按第一个字符排序（如问题标题所指定的那样），请使用以下方法：

  Order By LTrim(
           Replace(Replace(Replace(Replace(Replace(
           Replace(Replace(Replace(Replace(Replace(
             colname, '1', ''),'2',''),'3',''),'4,''),'5', ''),
                      '6',''),'7',''),'8',''),'9',''),'0','')) 

上面是一个猜测（我没有尝试过），但是思路是先从列值中删除所有数字字符，然后取剩余部分的第一个字符。

此外，如果这个方法可行，而且如果您有任何对数据库的开发访问权限（考虑DRY原则），我建议在该表上添加一个计算列（或对该表进行单独的视图），该计算列的定义使用上述表达式，以便这个镇名的“提取”可以供其他可能需要访问它的代码使用，而不必将这个表达式复制到每个需要它的地方。

- Charles Bretana

---

我认为这将是最好的方法。我不熟悉如何使用PHP编写表达式。你能建议一下最好的方法吗？ - iagdotme

为什么只取第一个字符？如果有两个名字以相同的第一个字符开头怎么办？ - Veger

好的，我会尝试这个。只是担心这会使查询变慢很多。我同意Veger的观点，它不应该只是第一个字符，可能是前三或四个字符比较保险。另外，我应该加一个连字符，因为有些地址在邮政城镇之前有连字符。 - iagdotme

如果你问我如果这是我的数据库和我的问题，我会怎么做，那么是的，我可能会这样做。但是，假设OP知道他所问的，我回答了他所问的问题，而不是我认为他应该问的问题... - Charles Bretana

@Baritoneuk，如果是这样，那么为什么你的问题标题是“按第一个字母排序....”？ - Charles Bretana

@Baritoneuk，如果您使用它，是的，请添加另一个替换破折号的方法，并且对于任何可能出现在第一个字母之前的其他字符，您希望排序开始... - Charles Bretana

Answer 2

你可以编写一个存储函数，返回从第一个字母字符开始的列的余数（可以使用 REGEXP 查找该索引）。然后按照存储函数排序。

编辑：在你的函数中，可以根据数据格式而不是用正则表达式，在“ ”(空格)上执行”substring_index”，并返回第一个空格的索引，然后调用子字符串来返回在第一个空格之后的字符串余下部分。

一旦创建了一个存储函数来返回数字后面的字符串，就可以像这样利用它：

order by yourfunctionname(postalTown)

存储过程

- Chris Kannon

---

这听起来很有趣，但我不太确定你的意思是什么，也不知道如何实现。 - iagdotme

Baritoneuk，我已经编辑好一个存储函数的教程。 - Chris Kannon

Answer 3

我首先想到的是在我的ORDER BY中添加0到9的数字。显然，你会发现糟糕的模式会产生糟糕的解决方案。 :) 正如上面那位先生所说，你应该考虑重新设计你存储城镇数据的方式。

ORDER BY REPLACE(REPLACE(REPLACE(FieldName, '1', ''),'2',''),'3','')  ETC.

- George Johnston

---

你能否使用正则表达式替换来代替嵌套的REPLACE函数？ - FrustratedWithFormsDesigner

我喜欢这种思路，但这可能有点费力。在MYSQL中是否有像PHP中那样删除数字字符的方法？ - iagdotme

好的，我知道这可能是一个有点糟糕的模式。但是还有其他选择吗？如果在某些国家的邮政城镇中城镇之前有数字，我该怎么办？ - iagdotme

我更深入地研究了国际地址格式- http://www.bitboost.com/ref/international-address-formats.html 从技术上讲，邮政城镇前的数字是邮政编码，但在某些国家，它应该出现在邮政城镇之前而不是之后（如英国）。最好将邮政编码放在邮政编码列中，然后通过检测国家，在需要时将邮政编码放在邮政城镇之前。听起来需要做很多额外的工作！ - iagdotme

Answer 4

创建一个视图，进行必要的翻译，然后在视图上进行查询？

- Unsliced