I've searched all over the internet to find some solution for varchar field with mixed numbers and text and it works perfect! (Fortunately all my records in this field starts with number and text is after)

I have a field in my database that is item items unique reference. Coming from different suppliers, the items have various alphanumeric ID's which cannot be changed to stop the database design being flawed.

Basically, some codes are just numeric, some are alphanumeric with a number first then the letter(s), some vice versa. Some may even have a combination of letters then numbers or vice versa.

X1, X2, X3....X10, 1, 2, 3, 1A, 1B, 1C

I've tried

ORDER BY col_name+0, col_name
ORDER BY col_name, col_name+0
ORDER BY ABS(col_name), col_name+0, col_name

as well as other combinations. One way will order the X1, X2, X3....X10 etc correctly as well as the 1, 2, 3 but the 1A, 1B, 1C are not in order. One way will order the 1, 2, 3 and 1A, 1B, 1C correctly by then order the X1, X2, X3 as X1, X10, X2, X3 etc.

I haven't tried it yet but this might be useful for situations like mine where I have varchar fields with filenames that are numbered such as filename_01.jpg, filename_02.jpg, etc. I don't know if this is what I need, but it doesn't mean the database is flawed necessarily.