1

I have a MySQL table with 'address' field with addresses in format:

StreetName 1
StreetName 10
StreetName 34
Streetname 8

How to make a MySQL Query that will resault:

StreetName 1
Streetname 8
StreetName 10
StreetName 34

Simple "order by address" doesn't work in this case, because "StreetName 8" is AFTER "StreetName 34".

vidit
  • 6,293
  • 3
  • 32
  • 50
user2296998
  • 11
  • 3

1 Answers1

0

If the street name wont have any spaces, this should work

SELECT * FROM table
ORDER BY CAST(SUBSTRING(address,LOCATE(' ',address)+1) AS UNSIGNED)

Update: If the street names have spaces, but the numbers are always at the end with a space preceding them eg. S Av 2, N Pike Street 5 etc, you can try this.. 

SELECT * FROM table
ORDER BY CAST(SUBSTRING_INDEX(address,' ', -1) AS UNSIGNED)

I have also assumed that the street numbers will be unsigned(not negative). A fiddle for you..

vidit
  • 6,293
  • 3
  • 32
  • 50
  • but it has spaces, so this solution is not good :( – user2296998 Jun 15 '13 at 16:35
  • Thank you, but it doesn't work. Is there a posibility to create new column with will only have street numbers and another, without it? Then I could sort first by street name, then, by street nuber by simple "order by 'streetname' asc, 'streetnumber', asc – user2296998 Jun 15 '13 at 17:49
  • May I know why it doesn't work? – vidit Jun 15 '13 at 17:51
  • it displayed only street names without numbers in address field, sorting also was strange, without any rule – user2296998 Jun 15 '13 at 17:59