title: Oracle PL/SQL Convert string into INTEGER (maximum value 2147483648)
body: <p>Please suppose you have a VARCHAR2 string of 6 character so composed:</p>
  
  <p>LETTER LETTER DIGIT LETTER LETTER LETTER</p>
  
  <p>where LETTER = A, B, C, ....., W, X, Y, Z
  and
  NUMBER = 0, 1, 2, 3, ....., 9</p>
  
  <p>The minimum string with the previous rules is: AA0AAA
  The maximum string with the previous rules is: ZZ9ZZZ</p>
  
  <p>The number of possible combinations is:</p>
  
  <p>26 * 26 * 10 * 26 * 26 * 26 = 118813760 </p>
  
  <p>How can I decode AA0AAA to 1, AA0AAB to 2, AA0AAC to 3, ....., ZZ9ZZZ to 118813760?</p>
  
  <p>I am programming in Oracle PL/SQL.</p>
  
  <p>Thank you in advance for your kind cooperation.</p>
  
  <p>This is my code but it is not optimized :(</p>
  
  <pre><code>SELECT   RIGA
    FROM   (SELECT   ROWNUM riga, alfa.*
              FROM   (WITH s1 AS (SELECT   'A' list1 FROM DUAL
                                  UNION ALL
                                  SELECT   'B' FROM DUAL
                                  UNION ALL
                                  SELECT   'C' FROM DUAL
                                  UNION ALL
                                  SELECT   'D' FROM DUAL
                                  UNION ALL
                                  SELECT   'E' FROM DUAL
                                  UNION ALL
                                  SELECT   'F' FROM DUAL
                                  UNION ALL
                                  SELECT   'G' FROM DUAL
                                  UNION ALL
                                  SELECT   'H' FROM DUAL
                                  UNION ALL
                                  SELECT   'I' FROM DUAL
                                  UNION ALL
                                  SELECT   'J' FROM DUAL
                                  UNION ALL
                                  SELECT   'K' FROM DUAL
                                  UNION ALL
                                  SELECT   'L' FROM DUAL
                                  UNION ALL
                                  SELECT   'M' FROM DUAL
                                  UNION ALL
                                  SELECT   'N' FROM DUAL
                                  UNION ALL
                                  SELECT   'O' FROM DUAL
                                  UNION ALL
                                  SELECT   'P' FROM DUAL
                                  UNION ALL
                                  SELECT   'Q' FROM DUAL
                                  UNION ALL
                                  SELECT   'R' FROM DUAL
                                  UNION ALL
                                  SELECT   'S' FROM DUAL
                                  UNION ALL
                                  SELECT   'T' FROM DUAL
                                  UNION ALL
                                  SELECT   'U' FROM DUAL
                                  UNION ALL
                                  SELECT   'V' FROM DUAL
                                  UNION ALL
                                  SELECT   'W' FROM DUAL
                                  UNION ALL
                                  SELECT   'X' FROM DUAL
                                  UNION ALL
                                  SELECT   'Y' FROM DUAL
                                  UNION ALL
                                  SELECT   'Z' FROM DUAL),
                          s2 AS (SELECT   'A' list2 FROM DUAL
                                 UNION ALL
                                 SELECT   'B' FROM DUAL
                                 UNION ALL
                                 SELECT   'C' FROM DUAL
                                 UNION ALL
                                 SELECT   'D' FROM DUAL
                                 UNION ALL
                                 SELECT   'E' FROM DUAL
                                 UNION ALL
                                 SELECT   'F' FROM DUAL
                                 UNION ALL
                                 SELECT   'G' FROM DUAL
                                 UNION ALL
                                 SELECT   'H' FROM DUAL
                                 UNION ALL
                                 SELECT   'I' FROM DUAL
                                 UNION ALL
                                 SELECT   'J' FROM DUAL
                                 UNION ALL
                                 SELECT   'K' FROM DUAL
                                 UNION ALL
                                 SELECT   'L' FROM DUAL
                                 UNION ALL
                                 SELECT   'M' FROM DUAL
                                 UNION ALL
                                 SELECT   'N' FROM DUAL
                                 UNION ALL
                                 SELECT   'O' FROM DUAL
                                 UNION ALL
                                 SELECT   'P' FROM DUAL
                                 UNION ALL
                                 SELECT   'Q' FROM DUAL
                                 UNION ALL
                                 SELECT   'R' FROM DUAL
                                 UNION ALL
                                 SELECT   'S' FROM DUAL
                                 UNION ALL
                                 SELECT   'T' FROM DUAL
                                 UNION ALL
                                 SELECT   'U' FROM DUAL
                                 UNION ALL
                                 SELECT   'V' FROM DUAL
                                 UNION ALL
                                 SELECT   'W' FROM DUAL
                                 UNION ALL
                                 SELECT   'X' FROM DUAL
                                 UNION ALL
                                 SELECT   'Y' FROM DUAL
                                 UNION ALL
                                 SELECT   'Z' FROM DUAL),
                          k1 AS (SELECT   '0' list3 FROM DUAL
                                 UNION ALL
                                 SELECT   '1' FROM DUAL
                                 UNION ALL
                                 SELECT   '2' FROM DUAL
                                 UNION ALL
                                 SELECT   '3' FROM DUAL
                                 UNION ALL
                                 SELECT   '4' FROM DUAL
                                 UNION ALL
                                 SELECT   '5' FROM DUAL
                                 UNION ALL
                                 SELECT   '6' FROM DUAL
                                 UNION ALL
                                 SELECT   '7' FROM DUAL
                                 UNION ALL
                                 SELECT   '8' FROM DUAL
                                 UNION ALL
                                 SELECT   '9' FROM DUAL),
                          s3 AS (SELECT   'A' list4 FROM DUAL
                                 UNION ALL
                                 SELECT   'B' FROM DUAL
                                 UNION ALL
                                 SELECT   'C' FROM DUAL
                                 UNION ALL
                                 SELECT   'D' FROM DUAL
                                 UNION ALL
                                 SELECT   'E' FROM DUAL
                                 UNION ALL
                                 SELECT   'F' FROM DUAL
                                 UNION ALL
                                 SELECT   'G' FROM DUAL
                                 UNION ALL
                                 SELECT   'H' FROM DUAL
                                 UNION ALL
                                 SELECT   'I' FROM DUAL
                                 UNION ALL
                                 SELECT   'J' FROM DUAL
                                 UNION ALL
                                 SELECT   'K' FROM DUAL
                                 UNION ALL
                                 SELECT   'L' FROM DUAL
                                 UNION ALL
                                 SELECT   'M' FROM DUAL
                                 UNION ALL
                                 SELECT   'N' FROM DUAL
                                 UNION ALL
                                 SELECT   'O' FROM DUAL
                                 UNION ALL
                                 SELECT   'P' FROM DUAL
                                 UNION ALL
                                 SELECT   'Q' FROM DUAL
                                 UNION ALL
                                 SELECT   'R' FROM DUAL
                                 UNION ALL
                                 SELECT   'S' FROM DUAL
                                 UNION ALL
                                 SELECT   'T' FROM DUAL
                                 UNION ALL
                                 SELECT   'U' FROM DUAL
                                 UNION ALL
                                 SELECT   'V' FROM DUAL
                                 UNION ALL
                                 SELECT   'W' FROM DUAL
                                 UNION ALL
                                 SELECT   'X' FROM DUAL
                                 UNION ALL
                                 SELECT   'Y' FROM DUAL
                                 UNION ALL
                                 SELECT   'Z' FROM DUAL),
                          s4 AS (SELECT   'A' list5 FROM DUAL
                                 UNION ALL
                                 SELECT   'B' FROM DUAL
                                 UNION ALL
                                 SELECT   'C' FROM DUAL
                                 UNION ALL
                                 SELECT   'D' FROM DUAL
                                 UNION ALL
                                 SELECT   'E' FROM DUAL
                                 UNION ALL
                                 SELECT   'F' FROM DUAL
                                 UNION ALL
                                 SELECT   'G' FROM DUAL
                                 UNION ALL
                                 SELECT   'H' FROM DUAL
                                 UNION ALL
                                 SELECT   'I' FROM DUAL
                                 UNION ALL
                                 SELECT   'J' FROM DUAL
                                 UNION ALL
                                 SELECT   'K' FROM DUAL
                                 UNION ALL
                                 SELECT   'L' FROM DUAL
                                 UNION ALL
                                 SELECT   'M' FROM DUAL
                                 UNION ALL
                                 SELECT   'N' FROM DUAL
                                 UNION ALL
                                 SELECT   'O' FROM DUAL
                                 UNION ALL
                                 SELECT   'P' FROM DUAL
                                 UNION ALL
                                 SELECT   'Q' FROM DUAL
                                 UNION ALL
                                 SELECT   'R' FROM DUAL
                                 UNION ALL
                                 SELECT   'S' FROM DUAL
                                 UNION ALL
                                 SELECT   'T' FROM DUAL
                                 UNION ALL
                                 SELECT   'U' FROM DUAL
                                 UNION ALL
                                 SELECT   'V' FROM DUAL
                                 UNION ALL
                                 SELECT   'W' FROM DUAL
                                 UNION ALL
                                 SELECT   'X' FROM DUAL
                                 UNION ALL
                                 SELECT   'Y' FROM DUAL
                                 UNION ALL
                                 SELECT   'Z' FROM DUAL),
                          s5 AS (SELECT   'A' list6 FROM DUAL
                                 UNION ALL
                                 SELECT   'B' FROM DUAL
                                 UNION ALL
                                 SELECT   'C' FROM DUAL
                                 UNION ALL
                                 SELECT   'D' FROM DUAL
                                 UNION ALL
                                 SELECT   'E' FROM DUAL
                                 UNION ALL
                                 SELECT   'F' FROM DUAL
                                 UNION ALL
                                 SELECT   'G' FROM DUAL
                                 UNION ALL
                                 SELECT   'H' FROM DUAL
                                 UNION ALL
                                 SELECT   'I' FROM DUAL
                                 UNION ALL
                                 SELECT   'J' FROM DUAL
                                 UNION ALL
                                 SELECT   'K' FROM DUAL
                                 UNION ALL
                                 SELECT   'L' FROM DUAL
                                 UNION ALL
                                 SELECT   'M' FROM DUAL
                                 UNION ALL
                                 SELECT   'N' FROM DUAL
                                 UNION ALL
                                 SELECT   'O' FROM DUAL
                                 UNION ALL
                                 SELECT   'P' FROM DUAL
                                 UNION ALL
                                 SELECT   'Q' FROM DUAL
                                 UNION ALL
                                 SELECT   'R' FROM DUAL
                                 UNION ALL
                                 SELECT   'S' FROM DUAL
                                 UNION ALL
                                 SELECT   'T' FROM DUAL
                                 UNION ALL
                                 SELECT   'U' FROM DUAL
                                 UNION ALL
                                 SELECT   'V' FROM DUAL
                                 UNION ALL
                                 SELECT   'W' FROM DUAL
                                 UNION ALL
                                 SELECT   'X' FROM DUAL
                                 UNION ALL
                                 SELECT   'Y' FROM DUAL
                                 UNION ALL
                                 SELECT   'Z' FROM DUAL)
                      SELECT   *
                        FROM   S1,
                               S2,
                               K1,
                               S3,
                               S4,
                               S5) alfa)
   WHERE       LIST1 = 'A'
           AND LIST2 = 'A'
           AND LIST3 = '1'
           AND LIST4 = 'A'
           AND LIST5 = 'A'
           AND LIST6 = 'A';
  </code></pre>