Sorting reference designators (i.e. 1A1, 10A1)

[chrscote]

I have a database that stores unit placements (called reference designators) based on their locations within a cabinet in a form of 1A1A1, 10A1, 12A2, 2A1 etc along with other data pertaining to the individual units such as width, height, weight. When I try to sort these in the database, I always get 10A1 appearing before 2A1, though I want it to be the other way around. Has anyone ever performed a similar sort in ASP? I think what I may need to do is place the individual fields in an array then change the reference designators so that each numeric value is a 3-digit number, then sort.
I know how to sort an array if it has a single dimension. However, these arrays will have 2 dimensions in which each record can contain as many as 10 fields. I'm not sure how to sort this array to keep all the data for a single record together. Does someone know how I can do this?


Chris

[Old Pedant]

First question: Why are you talking about having ASP do the sorting??? Why aren't you doing the sorting in SQL? You DO say the data is in a database, after all.

Yes, you have a poor design for the "unit placements". Probably what you should do is add a "normalizedUnitPlacement" field to the DB table and then perhaps use ASP to do a one-time loop through the table calculating the normalized form.

The normalized form could, indeed, use 3 digits for all numeric sections of the placement:
1A1A1 ==>> 001:A:001:A:001
10A1 ==>> 010:A:001
and then just doing an ORDER BY NormalizedUnitPlacement in the SQL will do the job.

You don't say if the alpha portions are ever longer than one character. If they, too, can be up to (say) three characters, then you'd need to pad them out, as well. Could use spaces of maybe the @ character, thus:
1A1A1 ==>> 001:@@A:001:@@A:001
10AC1 ==>> 010:@AC:001
etc.
[The colon separators aren't needed, but the aid in human readability.]