Order By problem

[Guest]

if a have a field of part numbers that was entered in random order like:

62A-1212
62A-44
62A-33
62A-1011

they will come out in ASC

as
62A-1011
62A-1212
62A-33
62A-44

which is not what i need... that is:

62A-33
62A-44
62A-1011
62A-1212

any way to modify the asc/desc ORDER BY command???

tnx in advance

[paross1]

What database is it? Each RDBMS has its own set of character and numeric functions that my allow you to manipulate the data in the ORDER BY clause.

Questions to answer:
Is the format the same for all entries, such as number, letter, dash, number, etc? If so, do you only need to sort by the number following the dash, or by the first number and then the number following the dash?

At any rate, there are ways to do things like taking a substring of the field from a particular starting character, then converting that substring to a number and using the resulting value in the ORDER BY, etc. Using regular expressions also come to mind.

More detail from you on your requirements may yield more comprehensive suggestions.

Phil