title: Number System Conversion - Base 10 to base x using SQL statements only
body: <p>I want to <a href="http://www.oracle-base.com/articles/misc/plsql-stop-making-the-same-performance-mistakes.php#stop-using-plsql-when-you-could-use-sql" rel="nofollow noreferrer">stop making the same performance mistakes</a> and need someone way steadier on SQL statements than me on this one.</p>
  
  <p>Basically I want my <a href="https://stackoverflow.com/questions/4459902/is-it-possible-to-have-alphanumeric-sequence-generator-in-sql/18476947#18476947">function</a>:</p>
  
  <pre><code>create or replace FUNCTION SEQGEN(vinp in varchar2, iSeq in INTEGER) 
  RETURN VARCHAR2 is vResult VARCHAR2(32);
    iBas INTEGER; iRem INTEGER; iQuo INTEGER; lLen CONSTANT INTEGER := 2;
  BEGIN
    iBas := length(vInp);
    iQuo := iSeq;
    WHILE iQuo &gt; 0 LOOP
      iRem := iQuo mod iBas;
      --dbms_output.put_line('Now we divide ' || lpad(iQuo,lLen,'0') || ' by ' || lpad(iBas,lLen,'0') || ', yielding a quotient of ' || lpad( TRUNC(iQuo / iBas) ,lLen,'0') || ' and a remainder of ' || lpad(iRem,lLen,'0') || ' giving the char: ' || substr(vInp, iRem, 1)); end if;
      iQuo := TRUNC(iQuo / iBas);
      If iRem &lt; 1 Then iRem := iBas; iQuo := iQuo - 1; End If;
      vResult := substr(vInp, iRem, 1) || vResult;
    END LOOP;
    RETURN vResult;
  END SEQGEN;
  </code></pre>
  
  <p>to be written with SQL statements only.</p>
  
  <p>Something like:</p>
  
  <pre><code>WITH sequence ( vResult, lSeq ) AS 
  (
    SELECT str, length(str) base FROM (SELECT 'abc' str FROM DUAL)
  
  )
  SELECT vResult FROM sequence WHERE lSeq &lt; 13
  </code></pre>
  
  <hr>
  
  <pre><code>if str = 'aB' output:           if str = 'abC' output:
  1               a                       a
  2               B                       b
  3           a   a                       C
  4           a   B                   a   a
  5           B   a                   a   b
  6           B   B                   a   C
  7       a   a   a                   b   a
  8       a   a   B                   b   b
  9       a   B   a                   b   C
  10      a   B   B                   C   a
  11      B   a   a                   C   b
  12      B   a   B                   C   C
  13      B   B   a               a   a   a
  </code></pre>
  
  <p>and if str = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' then:</p>
  
  <pre><code>SELECT vResult FROM sequence 
  WHERE vResult in ('0001','0002','0009','000A','000Z',
                    '0010','0011','001A','ZZZZ')  --you get the idea...
  </code></pre>
  
  <p>I have found some questions at Stackoverflow that are fairly related.
  <a href="https://stackoverflow.com/questions/2568668/base-36-to-base-10-conversion-using-sql-only">Base 36 to Base 10 conversion using SQL only</a> and
  <a href="https://stackoverflow.com/questions/3086083/pl-sql-base-conversion-without-functions">PL/SQL base conversion without functions</a>.
  But with my current knowledge of SQL I am not quite able to hack this one...</p>
  
  <hr>
  
  <p>EDITED:<br>
  Or well, it is almost like this one:</p>
  
  <pre><code>select sum(position_value) pos_val from (
    select power(base,position-1) * instr('abc', digit) as position_value from (
      select substr(input_string,length(input_string)+1-level,1) digit, level position, length(input_string) base
        from (select 'cc' input_string from dual)
        connect by level &lt;= length(input_string)
    )
  )
  </code></pre>
  
  <p>Excepted that I want to give the pos_val as a parameter and get the input_string out...</p>