CODEWITHSUNDEEP
phpmysql
Jon
Jon

Reputation: 93

ORDER BY letters and not numbers of a field

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

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

Upvotes: 2

Views: 677

Answers (3)

Federico Cristina
Federico Cristina

Reputation: 2223

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

Upvotes: 2

Tim Coker
Tim Coker

Reputation: 6524

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.

Upvotes: 4

user347594
user347594

Reputation: 1296

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

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

Upvotes: 2

Related Questions