Question sorting alphanumeric characters

[isi_rajah19]

[FONT=Verdana, Arial, Helvetica][FONT=Verdana, Arial, Helvetica]Hi i am currently doing a project in which the database needs to sort the lot numbers
prefix(preffix.text)
lotnum (lotnumber.text)
suffix (Suffix.text)

if i do a normal sort i get
a1a
1a
1
2
100

then prefix to the order by
and get this result
1
a1a
1a
2
100

i have added the suffix as well and returns the same result

i need to order it as follows

1
1a
2
100
a1a

Please could someone help me on this[/FONT][/FONT]

 

[Dunfiddlin]

There's really no logical system that would result in such a sorting so even a custom algorithm would struggle. I would suggest that you need a rethink of your numbering system!