Sort numbers with letters numerically and alphabetically

Question

I have a Database on my website with a Long List of #'s (Product #'s) all containing letters (Exp. TC-345, TC-234 or HC-236W 123-234-PWD...)

Can we numerically and alphabetically sort the #'s on the website?

Currently we store it alphabetically, so the order is (10-PDW, 100-PDW, 110-PDW 2-PDW) We would like to change it into (2-PDW, 10-PDW, 100-PDW, 110-PDW)

My developers say "The colorway number can never be sorted numerically. We would need to add another numeric field to the database for all the colorways and then sort that field numerically. Right now those numbers are in alphabetical order."

How do you sort a numbers with letters? We'd like to avoid adding a Numeric Field - that's just extra work. Is there any new technique out to do this?

score 0 · Answer 1 · answered Aug 01 '13 at 14:22

It is possible. One way would be using a function for weighting strings, which gives far more weight for numbers than letters. Something like this:

letbers = ["10-PDW", "100-PDW", "110-PDW", "2-PDW"]

def weight(letber):
    if letber == "":
        return 0
    n = ord(letber[-1])
    if letber[-1] in "0123456789":
        n *= 256^6                  # 6 because maximum key length is 6
    return 256*n + weight(letber[:-1])

print sorted(letbers, key = weight)

Thanks you i will forward this, he is on vacation :) – Christina Glover Aug 01 '13 at 14:29 — Christina Glover, Aug 01 '13 at 14:29

Mikaël Mayer · Answer 2 · 2014-03-18T13:35:05.107

If you add the following transformation function (in scala), then you can alphabetically and numerically sort all strings:

def transformed(s: String): String = {
  s.replaceAll("""(?<=[^\d]|^)(\d)(?=[^\d]|$)""","""000$1""")
   .replaceAll("""(?<=[^\d]|^)(\d\d)(?=[^\d]|$)""","""00$1""")
   .replaceAll("""(?<=[^\d]|^)(\d\d\d)(?=[^\d]|$)""","""0$1""")
}

Basically it replaces every number occurence by a fixed width integer, so that the alphabetical sorting equals the numerical sorting in that case.

Test on your input:

> val s = List("10-PDW", "100-PDW", "110-PDW", "2-PDW")

> s.sortBy(transformed)

res2: List[String] = List(2-PDW, 10-PDW, 100-PDW, 110-PDW)

This works only if you are sure that all numbers are below 9999. If you have more digits, then you should consider to expand the function or do something else.

Sort numbers with letters numerically and alphabetically

2 Answers2

Linked