3 Replies Latest reply: Aug 1, 2011 12:44 PM by John Witherspoon RSS

    Convert numeric base90 to base10

    Derek Keeble

      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.

        • Re: Convert numeric base90 to base10
          John Witherspoon

          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

          • Convert numeric base90 to base10
            Derek Keeble

            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