Correct Sort Numbers Stored As Text

[twodaend]

I have some document numbers stored as text values that I would like to sort by in the correct order. However, what I'm getting is 1-101, 1-10A, 1-11, 1-14, 1-17A, 1-17A.i, 1-19, 1-2, 1-21, 1-30, 1.35, 1.47, 1-4-A, 1-5, 1-7, 1-A, 1-B, 1-C. These are the document's ID so that is why the inconsistancy. I wish they would have done better with this, but these ar old docs and and all I have to work with. This is just a small set. I have many more begining with 2's, 3's, and so on.

I was wondering how can I get these to be sorted in the correct order from smallet to largest. I.E.
1-2, 1-4-A, 1-5, 1-7, 1-10A, 1-11, 1-14, 1-17A, 1-17A.i, 1-19, 1-21, 1-30, 1.35, 1.47, 1-101,1-A, 1-B, 1-C

The ABC can be at the beggining or end, does not matter just as long as the numbers are correct.

Is this at all possible. Thanks for the help.

[MikerRoo]

These are stored in a DB, right?

It is very important to know which one (Access, MS SQL. etc.) because the needed syntax is DB specific.

Also is "1-101' one id or a range?

[twodaend · Accepted solution]

It is stored in a MS Access DB. I think I figured out how to get things in the correct order. Since there is an inconsistancy in the naming convention, I made a way to make it consistent. I have 2 columns, 1 with the document ID number and the other with just the number of the document (1-17A would have 17 for the second column.) This way I can sot first based on the second column since they are all numeric and the sort for the first column of I have duplicated in the second column.

For example 1-17A and 1-17A.i would both have 17 in the second column, but by sorting by both columns, the order is correct and 1-17A comes before 1-17A.i. For dcoument ID's that don't have a number and rather a letter such ast 1-A, I assing a number of 1000 for A, 1001 for B and so on. This way they too have a number and will usually appear at the end of the list.

[brixon]

My first concern would be the inconsistent way the values are stored:
1-2
1-4-A
1-17A
1-A
1.35

Depending on how you are using the data and how often they change, you might want to create a look-up table that holds the native ID (like above) and then a well formed id (or series of columns to make the id) that will search and sort correctly.