Natural Sort on data mixing text and numbers (and then more text *sometimes*)

I am working on a site for a gu开发者_运维百科y who runs livestock auctions. He will enter the animals as Lot 1, Lot 2, Lot 3, Lot 3a, Lot 4, ... Lot 100, ... Lot N. Since this is a mix of numbers and text, I was first ordering by Length(LotName), LotName.* That worked until I entered an "add on" animal (specified by the 'a' at the end).

Is there anyway I can get the data to sort right through the SQL?

*Found on this site, thanks!

---

Through pure SQL? Not easy!

We did this in code on my project, and basically, you apply a regular expression that matches runs of digits or non-digits (something like ([0-9]+)|([^0-9]+)), turns a string into a tuple of such runs, converts digit runs into integers, then sorts the tuples with a pairwise comparison. For example:

"Lot 1"   -> ("Lot ", 1)
"Lot 2"   -> ("Lot ", 2)
"Lot 3"   -> ("Lot ", 3)
"Lot 3a"  -> ("Lot ", 3, "a")
"Lot 100" -> ("Lot ", 100)

The pairwise comparison then (a) sorts integers before strings, (b) sorts integers in natural order, and (c) sorts strings in natural order.

If you were able to apply regular expressions to columns for sorting, you could use one to 'normalise' the numbers into digit strings of a fixed length, padded with zeroes. Like this:

"Lot 1"   -> "Lot 0001"
"Lot 2"   -> "Lot 0002"
"Lot 3"   -> "Lot 0003"
"Lot 3a"  -> "Lot 0003a"
"Lot 100" -> "Lot 0100"

Which should sort correctly. However, whilst you can do this in Oracle and some other databases, in MySQL, you need to use a user-defined function.

However, you could do it outside the database. Add a column to the lot table to store the normalised lot name, and whenever you insert a row from your code, generate the normalised form and store it. You can then sort using that column.