Page 1 of 1
Number and Letter Order
Posted: Sun Feb 28, 2010 4:41 am
by espy69
Hi
I hope someone can help. I am having trouble ordering letters and numbers in the same column. I am retrieving information from a mysql database and how I want the output to look is :
1
2
3
upto 20
F
H
Z
If I use ABS() it pushes the letters to the top:
F
H
Z
1
2
3
upto 20
If I take ABS off it puts it in the order of 1,10,11 and so on
Is there anyway either in the mysql query or php that I can output in the correct order
Many thanks
Steve
Re: Number and Letter Order
Posted: Sun Feb 28, 2010 10:15 am
by davex
Does not just ORDER BY fieldname(ASC); work? - I think the numbers would come first in the order.
Cheers,
Dave.
Re: Number and Letter Order
Posted: Sun Feb 28, 2010 11:22 am
by espy69
Hi
Thanks but tried that and it doesnt work as mysql sees 10 as the next number after 1 e.g 1, 10, 11 etc..
Thanks
Steve
Re: Number and Letter Order
Posted: Sun Feb 28, 2010 11:57 am
by davex
Hi,
Ok sorry.
So basically you want it to sort as if it was number base 36 (0-9 then A-Z) so the following data:
0, 10, A, B, AA, A1, 120, ABC
Would come out as:
0, A, B, 10, A1, AA, 120, ABC
In that case I think ORDER BY LENGTH(field) ASC, field ASC
Should work, sorting by length in the first instance and then sorting all of the same length by the field value.
Cheers,
Dave.
Re: Number and Letter Order
Posted: Sun Feb 28, 2010 12:04 pm
by espy69
Hi
Thanks but Im not testing the length of the value, I just want it to return numbers in order i.e 1 to 20 followed by letters.
Many thanks
Steve
Re: Number and Letter Order
Posted: Sun Feb 28, 2010 12:30 pm
by davex
Right - sorry again for not quite getting it.
This is messy as anything but seems to do the job:
ORDER BY testfield REGEXP '^[0-9]*$' DESC,CAST(testfield AS SIGNED INTEGER) ASC, LENGTH(testfield) ASC, testfield ASC
So it...
Orders numeric-only (REGEXP)
Orders those numbers correctly (REGEXP ASC would give the letters first so need to swap the order) (CAST)
Orders by length (so AA comes after B)
Orders by ascending order (so A comes before Z)
So with data of:
0, 10, A, B, AA, 120
This will return the order:
0, 10, 120, A, B, AA
Is that right? Sorry if I've missed it again. If I have can you give an example dataset and how you would like it ordered fully?
Thanks,
Dave.
Re: Number and Letter Order
Posted: Sun Feb 28, 2010 12:37 pm
by espy69
Thanks Dave
Will give that a go and get back to you if I come across any probs
Thanks again Steve
Re: Number and Letter Order
Posted: Sun Feb 28, 2010 12:55 pm
by espy69
Excellent, works like a charm
Many thanks
Steve
Re: Number and Letter Order
Posted: Sun Feb 28, 2010 1:00 pm
by davex
No problem - sorry it took me so long to get there.
And I'm sure there are better ways but can't think of any.
Regards,
Dave.
Re: Number and Letter Order
Posted: Mon Mar 01, 2010 9:58 am
by pickle
Try doing a "natural sort". MySQL has no function for doing that, but I do remember seeing a simple trick that didn't involve regex (which should be avoided if possibly simply due to the overhead it incurs).