Convert numeric base90 to base10

[#1]

Hi

I have a legacy system that has numeric data stored in base90 in one of our tables.

I want to extract and convert to base10 (A bit easier to work with !!!!)

Has any one done this before and if so what is the trick.

(Not sure if any relevence but the db that I am pulling from is Visual Foxpro using ODBC driver)

Looking forward to any help.

[johnw · Accepted solution]

By base 90, you mean that '15' should be converted to 1*90 + 5*1 = 95, and '321' to 3*90*90 + 2*90 + 1 = 24481?

What characters are you using once you get past digits 0-9?  I'm guessing A-Z, but that leaves a whole lot of characters to go.

I'm not sure this is a good way, but you could make a map of your base 90 digits converted to base 10 value:

Base90Map:
LOAD * INLINE [
Base90, Base10
0,0
1,1
2,2
...
9,9
A,10
B,11
...
Z,35
...
];

And then apply the map to each digit of your input field using the appropriate power of 90:

,applymap('Base90Map',mid(Base90Input,1,1))*pow(90,0)
+applymap('Base90Map',mid(Base90Input,2,1))*pow(90,1)
+applymap('Base90Map',mid(Base90Input,3,1))*pow(90,2)
+applymap('Base90Map',mid(Base90Input,4,1))*pow(90,3)
... as Base10Result

[#3]

The therory was perfect - JW what a star.

Took longer to work out the character set than write the script !!!!!

Only minor issue was the apply map did not work but nothing a left join didn't resolve.

Thanks

[johnw]

It looks like I was missing some closing parens (fixed above).  I'm not sure if that was the problem with the applymap, but left join is probably fine.