ORDER BY letters and not numbers of a field

Question

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

• 123 Main Street
• 456 Second Street
• 234 Third Street

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

Answer 1

Assuming street number and street name are always separated by space.

SELECT ...
...
ORDER BY SUBSTRING(address, FIND_IN_SET(' ', address)+1)

Answer 2

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.

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

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.

Answer 3

Although you can apply a SUBSTRING in the ORDER BY clause (like johncatfish explained), looks like you should re-desing that table, separating that one field into Street and Number fields.

Under that scenario, the task becomes easy:

SELECT Number, Street
FROM Addresses
ORDER BY Street, Number