15

Say I am displaying data to a user in a grid, report, drop down box, etc. The data in question contains a field called Invoice Number. This field contains only numerics 2/3 of the time, but the other 1/3 of the time is a mixture of alpha-numerics.

Because of this, any sorting algorithm would sort alpha numerically instead of numerically. For this reason, 1000 would come far before 99. Because the first digit is sorted on - not the entire value. Other items like 000333 would not be treated as "333" and would show up before 222.

Is there any standard here? Should I be attempting to convert all values to a numeric value and sort those differently? How would they get integrated back together? Or is alpha-numeric sorting the correct way to go?

Improve this question
7
  • If you have the ability to say, use substring to truncate the first three digits which have the alphabets and sort solely based on the last three digits, in your case say ABC333 and 000222 - you could implement this. Sure, while displaying it would come in the order 000222 and then ABC333. However, visually it would be a little confusing to see zeros against few numerals and alphabets against others. What do the alphabets suggest?
    – Swapnil Borkar
    Jun 9, 2016 at 13:58
  • Varying lengths so no SubString. I think seeing "10" come before "9" (actually FAR before 9) would be confusing to users who don't use alpha numerics often. On the other hand a pure mixture of these really can't be sorted any nice way that I can see.
    – Paul
    Jun 9, 2016 at 14:23
  • 1
    If these are invoices, can you sort them by Date or is it explicitly needed to be sorted according to the Invoice number itself? Maybe you could use another Column from the table and sort them accordingly. Assuming these are generated in increasing order, a chronological order of invoices with the most frequent one generated at the top would make sense, no? I am not sure on what the drop down is actually for or how can the user benefit from the sort. If you could enlighten us more on the usage of the field, it would be a lot more helpful!
    – Swapnil Borkar
    Jun 9, 2016 at 14:42
  • It's up to the user. They can view this data via reporting, via an accounting screen that lets them check balances against Net 30 (see all invoices from one vendor), etc. The user chooses how they want the screen or report sorted. Date is an option. But so is invoice number. These are not generated by my application. You order supplies, parts, etc from vendors. That specific vendor kicks back an invoice number to you. Like if you ordered from five online stores and got five order numbers. Some are numeric some are not.
    – Paul
    Jun 9, 2016 at 14:46
  • You should convert these numbers to a numeric format. Use this only to sort the Alphanumeric values. The converted numbers need not be shown to the user to cause more confusion. I would however highly recommend you to have another column for example - Date to complement the Alphanumerals. Since sorting Alphanumerals will be a little confusing, another column to help the user figure out the relevant date or something else would help a lot.
    – Swapnil Borkar
    Jun 9, 2016 at 14:59

1 Answer 1

Reset to default
18

Starting with Windows 7, Microsoft changed their default method of sorting directories by file name to use "numeric" sorting. (Some info here)

While I couldn't find a specification of its behavior, I managed to reverse engineer it. This algorithm should compare two alphanumeric strings and determine which one comes first.

  • Split each filename into alphabetical and numeric parts; i.e. the name text123moretext456 becomes the list {"text", "123", "moretext", "456"}

  • For each part in the two split names, perform the following comparison:

    • If both parts are strictly numeric, compare them as numbers
      • If the numbers are the same, compare them as strings
      • If the strings are the same, move on to the next part
    • If both parts are strictly letters, compare them as strings
      • If they're the same, move on to the next part
  • If you run out of parts, the name with the fewest parts comes first

You end up with an ordering like the following:

2
12
200000
1000000
a
a12
b2
text2
text2a
text2a2
text2a12
text2b
text12
text12a
Improve this answer
2
  • This is exactly what I was looking for, and the resulting sort order makes sense. Thank you!
    – Paul
    Jun 9, 2016 at 16:48
  • 4
    This is also known as "natural order sorting", "natural sort", or "human sorting", for those who come across this answer hoping to find an implementation - these are the keyword you'll want to use.
    – Duroth
    Sep 23, 2021 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.