按字母排序而不是字段编号 [英] ORDER BY letters and not numbers of a field

问题描述

有没有办法通过第一个字母排序mysql结果并忽略数字?例如，我有一个地址列表:

Is there a way to order mysql results by the first letter and ignore numbers? For example, I have a list of addresses:

• 大街123号
• 第二街456号
• 第三街234号

，我想按街道名称排序，而忽略街道编号.有没有简单的方法可以做到这一点?

and I want to order by the street name and ignore the street number. Is there an easy way to do this?

推荐答案

实际上没有一种干净的方法可以100％可靠地获得您想要的东西，而无需将数字和街道分为两个不同的领域.

There's not really a clean way to 100% reliably get what you want, without the number and street being split into two different fields.

像order by SUBSTRING(address, LOCATE(' ', address))这样的东西会带您到那里.

Something like order by SUBSTRING(address, LOCATE(' ', address)) would get you mostly there.

这是从第一次出现空格字符到结尾获取地址的子字符串.有效地关闭了"123 Main Street"的"123"，因此您要按"Main Street"进行排序.如果您在街道名称前有多个空格的地方，...祝您好运.从自动化的角度来看，您实际上无能为力.

What this is doing is getting the substring of the address from the first occurrence of a space character to the end. Effectively lopping off the '123' of '123 Main Street', so you're sorting by 'Main Street'. If you have something where there are multiple spaces before the street name ... good luck. There's not really much you can do from an automated perspective.

在大型数据库设置中，这将不会非常有效.在较小的环境中，这可能会很好.如果您的环境较大，或者这对您不起作用，那么您将必须设置作业以将地址字段预先拆分为另一个字段以进行排序.

THIS WILL NOT BE VERY PERFORMANT IN A LARGE DB SETTING. In a smaller environment, this will probably be fine. If your environment is larger, or this doesn't work for you, you're going to have to set up a job that pre-splits the address field to another field for sorting purposes.

这篇关于按字母排序而不是字段编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！