I'm using PHP and MySQL.
I have $pallet and it has values like this:
P01
P02
P12
P20
P05
How can I sort these values as:
P01
P02
P05
P12
P20
Add a comment
|
2 Answers
If the values always start with 'P' then you could strip out the 'P', convert the rest into a number and then ORDER BY that number ASC. Hence:
select yourcolumn
from (
select cast(replace(yourcolumn,'P','') as unsigned) as rank,
yourcolumn from yourtable) t
order by t.rank asc;
EDIT
Here is an example of how sorting alphabetically works:
create table pallet (id int unsigned not null primary key auto_increment,
pallet varchar(5));
insert into pallet (pallet) values ('P01');
insert into pallet (pallet) values ('P02');
insert into pallet (pallet) values ('P12');
insert into pallet (pallet) values ('P20');
insert into pallet (pallet) values ('P05');
insert into pallet (pallet) values ('P145');
select *
from pallet
order by pallet asc;
Result:
id pallet
-- ------
1 P01
2 P02
3 P05
4 P12
5 P145
6 P20
Not quite what we wanted!
select pallet
from (
select cast(replace(pallet,'P','') as unsigned) as rank,
pallet from pallet) t
order by t.rank asc;
Result:
id pallet
-- ------
1 P01
2 P02
3 P05
4 P12
5 P20
6 P145
Comments
Do they all start with a P? If so, you can just sort($pallet) or do an ORDER BY yourcolumnhere ASC since sorting them in an alphabetical order will be enough.
4 Comments
Tom Mac
Careful. This will only work if the number after the 'P' remains less than 100. A simple
ORDER BY yourcolumnhere ASC would not work if the value 'P145' was included. Something to bear in mind!
xdazz
@TomMac Why this will not work if the number after 'P' big than 100?
Tom Mac
@xdazz It won't work since MySQL will be sorting alphabetically instead of numerically on the values after the 'P'. Hence if you were to add 'P145' into the mix and sorted alphabetically you'd end up with: P01, P02, P05, P12, P145, P20 instead of P01, P02, P05, P12, P20, P145
Kaivosukeltaja
True, I made the assumption that the numbers won't exceed 99 because they are zero padded for two digits.