Question 20716 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL Order By Street Name then Street Number, Same Column – Mysql

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!

Tags:

2

Answers


  1. 0

    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)

    Login or Signup to reply.
  2. 0

    You can try with using Locate & Reverse functions in mysql..I just extracted the middle name & sorted.

    https://dbfiddle.uk/157f2xxv

    select * from data order by (select substring(address,locate(" ",address),length(address)- (locate(" ",reverse(address))+locate(" ",address))))

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search