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

[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

[yamaharuss]

You may want to consider padding your designators using three or four digits depending on how high they go.

1A1A1 becomes 001A1A1
10A1 becomes 010A1
etc.

I don't see any other way you're going to sort even using an array without some pretty clunky code.

If your database is not too large you could loop through the fields and pad them one at a time then be done with it. That is if it's OK to change the digits.

[Kuriyama]

You may want to consider padding your designators using three or four digits depending on how high they go.

1A1A1 becomes 001A1A1
10A1 becomes 010A1
etc.

I don't see any other way you're going to sort even using an array without some pretty clunky code.

If your database is not too large you could loop through the fields and pad them one at a time then be done with it. That is if it's OK to change the digits.


I agree with this approach. You can probably write a stored procedure about that will do this on the file so that you do not have to change your data in your DB.