SQL Order By Street Name then Street Number, Same Column

Question

I'm trying to order a list of addresses by street name, then street number. Both forms of this data reside in the same column, 'address' within my schema, 'data'.

The data have a similar format to:

• 27 Longshire Cir
• 109 Dewey Ln
• 26 Zeke Blvd
• 1 Longshire Cir
• 26 Albatross Rd

I've tried:

SELECT * FROM data ORDER BY address;

Which gives me:

• 1 Longshire Cir
• 26 Albatross Rd
• 26 Zeke Blvd
• 27 Longshire Cir
• 109 Dewey Ln

I have also tried:

SELECT * FROM parcel_data.parcels ORDER BY CONVERT(SUBSTRING_INDEX(address, ' ', 1), UNSIGNED INTEGER);

Which gives me a list that has no attention to the order of alphabetization, but follows the numbers. This would look something akin to:

• 1 Longshire Cir
• 26 Zeke Ln
• 26 Albatross Rd
• 27 Longshire Cir
• 109 Dewey Ln

Ideally, the output I'm looking for is:

• 26 Albatross Rd
• 109 Dewey Ln
• 1 Longshire Cir
• 27 Longshire Cir
• 26 Zeke Blvd

Putting alphabetization of street names first, then ordering the numbers.

How do I do that?

TIA!

Answer 1

In MySQL, you would just do:

order by
  substring_index(address,' ',char_length(replace(address,' ',''))-char_length(address)),
  0+address,
  address

(the last just for non-numeric house numbers)