Hi MySQL experts, How do we sort a field with alphabets+numbers using order by? The field looks like "AB123 - ...", "AB124 - ...", "AB1000 - ...", "AB101 - ...". The result should be AB101 -..., AB123 -..., AB124 - ..., AB1000 - ... not AB123 - ..., AB124 - ..., AB1000 - ..., AB101 Thanks for any help in advance.
First the admonishment: you are storing distinct and significant pieces of data in the same field and shouldn't be. Because the character portion and the numeric portion of that field need to be worked with seperately, they should be stored that way--in 2 different fields. That would let you easily order that data just like you want. That would be the easiest way to do that. What you can do to make yours work is something in that same spirit. In your query breakout that data into 2 different fields using MySQL string manipulation functions: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Have the character data be one field in your query and the numeric portion another, then use both those fields in the ORDER BY section of your query. First order by the character field, then by the numeric field.
Plog is correct - think as if numbers are letters. In the real world, 101 would be before 1000 for sure but in sorting, 1000 comes before 101. Imagine 0 = X and 1 = Y. In your example, we'd have ABYXY and ABYXXX. When sorting, ABYXXX comes before ABYXY when sorting alphabetically because the fifth letter in ABYXXX (X) comes before the fifth letter in ABYXY (Y). In your case of AB1000 versus AB101, the fifth character in AB1000 comes before the fifth character in AB101 . To humans this seems illogical but to computers it makes perfect sense. As plog suggested, in order to sort it they way you wish, numbers need to be treated as numbers, not characters as they are now and you would need to do string manipulation on your results.
Ideally the database should have number and characters in 2 different fields. But the database given to me is not allowed to be modified. That's why I ask for experts' help. I did try using substring but neither the character length nor the number length is same for each record. Any more tips are appreciated here.
Since the numeric data doesn't always occur at the same place in the field, you need to use the REGEXP function of MySQL. This will tell you exactly where those numbers occur in the field and where you need to split it. I'm not that great with regular expressions so I can't help you specifically, but I do know that's the function you need to look into.