Revision conversion to a number suggestions ?

[Jon]

I have a problem with Revisioning conversions. I wonder if any one has any
ideas on how to do this better. One of the elements in my database is
drawing numbers and their Revision letter. ie drawing GA123 Revision A . the
next drawing would be GA123 B and so on. My problem is when the drawing
rolls all the way to GA123 AA or Even 3 Letters in the revision ie Rev ABZ.
Currently i take the asc value of each letter and add them togeter to get a
number representing the revision. example Rev AB would be 65+66 = 131 so
when i compair drawings to see if a new one exists i can just look at the
number instead of trying to compair the string letters of the revision. the
problem is its breaking now with this asc to number conversion. for example
AY is 65+89=154 and Bk is 66+75=141 Bk is newier but its asc total value is
less than the old AY rev.
thanks

 

[Daryl S]

Jon -

I could see where revision AK has the same value as revision KA, etc.

Why aren't you just using revision numbers instead, like revision 1,
revision 2, etc.? Life would be simple. If the user's require the A, B, C
schema, then you could always have a lookup table for values as high as you
want to go (e.g. 1 = A, 2 = B, 27 = AA, etc.). Store the numeric revision in
the database, and display the alpha value to the users when needed.

In a more general light, as far as conversions go, to prevent the problem
you are having, you need to decide the max number of values per position, and
design a conversion scheme around that. If you are using only upper-case
alpha characters (or all alphas without case sensitivity), then you can have
up to 26 values in each position. If you use alpha-numerics and are case
sensitive, then you could have up to 62 values in each position. If you want
this to be converted to a numeric value, then each position must allow for
all values. Since we are normally decimal-minded, you could assign the
right-most values to be 1-99, which would cover all the examples in this
paragraph. The next value would need to start after 99, so you could
multiply the value of these by 100. For example:

A=1
B=2
....
Z=26
AA = 1*100 + 1 = 101
AB = 1*100 + 2 = 102
AZ = 1*100 + 26 = 126
BA = 2*100 + 1 = 201
BB = 2*100 + 2 = 202
....

This way, each character value provides a unique numeric value, even though
not all numeric values will be used. The third character from the right
would then need to be multiplied by 10000 to ensure unique values, etc.

 

[Jon]

Thanks for the reply Daryl,
why would the 3rd character be multiplyed by 10,000 ?

it looks like adding a zero to each letter place holder might work

 

[Daryl S]

Jon -

It will depend on how many combinations you can have in each position. If
it is 10 or less (assuming 00 is for no value), then one digit per position
will work. If it is 100 or less, (as in my example), then you need two
digits per position. So the first position (e.g. first character of the
string) would convert to two positions (00 to 99), the second position would
take the next two digits (0000 to 9900), the third position would take the
next two digits (000000 to 990000). You sum the values of the three
converted numbers to get your unique value.

ABA converts to 010201 because the first position (left-most) A converts to
01, the second position B converts to 0200, and the third postition A
converts to 010000. Add these together to get 010201.

If you only add one digit per letter, then what happens when you get to J or
K or Z? Your results will no longer be unique. If you multiplied each
successive converted value by only 10, then how would you know what 234 was?
It could be UX (210 + 24) or WD (230 + 4) or BCD (200 + 30 + 4)?