Number and Letter Order

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
espy69
Forum Newbie
Posts: 5
Joined: Sun Feb 28, 2010 4:33 am

Number and Letter Order

Post 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
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: Number and Letter Order

Post by davex »

Does not just ORDER BY fieldname(ASC); work? - I think the numbers would come first in the order.

Cheers,

Dave.
espy69
Forum Newbie
Posts: 5
Joined: Sun Feb 28, 2010 4:33 am

Re: Number and Letter Order

Post 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
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: Number and Letter Order

Post 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.
espy69
Forum Newbie
Posts: 5
Joined: Sun Feb 28, 2010 4:33 am

Re: Number and Letter Order

Post 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
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: Number and Letter Order

Post 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.
espy69
Forum Newbie
Posts: 5
Joined: Sun Feb 28, 2010 4:33 am

Re: Number and Letter Order

Post by espy69 »

Thanks Dave

Will give that a go and get back to you if I come across any probs

Thanks again Steve
espy69
Forum Newbie
Posts: 5
Joined: Sun Feb 28, 2010 4:33 am

Re: Number and Letter Order

Post by espy69 »

Excellent, works like a charm

Many thanks

Steve
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: Number and Letter Order

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Number and Letter Order

Post 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).
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply