MySQL - Sort asc with text in the begginig

Question

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 · Answer 1 · 2013-06-15T16:47:22.270

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..

edited Jun 15 '13 at 16:47

answered Jun 15 '13 at 16:33

vidit

6,135
3
29
47

but it has spaces, so this solution is not good :( – user2296998 Jun 15 '13 at 16:35
@user2296998- I have updated my answer. – vidit Jun 15 '13 at 16:41
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

MySQL - Sort asc with text in the begginig

1 Answers1