Alphanumrical Ordering of Varchar Field

Requirements were to add a field in which an alphanumerical ‘weight’ could be added for a product. This weight should then be used to display the products in order of ‘weight’.

Example values: 10a, 10b, 10c, 11g, 14f, 12a, 12aa etc.

SO i have tried selecting from the table and then ordering by the weight column, but the results are not as expected. The ordering is quite messed up with actual pattern to recognize.

Here are my results: 13k, 13l, 14c, 14d, 14e, 15a, 15b, 15c, 15e, 15f, 15g, 1a, 1b, 1c, 2a, 2b

As you can notice some kind of a switch is made after 15g. Instead of returning 1a etc before 13k etc, it returns it after 15g.

Here is my basic table structure:

CREATE TABLE IF NOT EXISTS `product` (
  `prd_id` int(11) NOT NULL auto_increment,
  `prd_no` varchar(255) default NULL,
  `prd_weight` varchar(50) default NULL,
  `prd_code` varchar(255) default NULL,
  `prd_description` varchar(255) default NULL,
  PRIMARY KEY  (`prd_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Can anybody help?

As you say yourself, it’s an alphanumeric field. So ‘a’ comes after ‘1’-‘9’. The sort is working just fine.
You could change ‘1a’ ‘1b’ etc in ‘01a’ ‘01b’…

Thank you very much I see what you mean when you say it is doing exactly as it suppose. It is ordering it one character at a time from left to right, with numeric evaluated before alphabetic chars.

:slight_smile: