Number System Conversion - Base 10 to base x using SQL statements only

Question

I want to stop making the same performance mistakes and need someone way steadier on SQL statements than me on this one.

Basically I want my function:

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 > 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 < 1 Then iRem := iBas; iQuo := iQuo - 1; End If;
    vResult := substr(vInp, iRem, 1) || vResult;
  END LOOP;
  RETURN vResult;
END SEQGEN;

to be written with SQL statements only.

Something like:

WITH sequence ( vResult, lSeq ) AS 
(
  SELECT str, length(str) base FROM (SELECT 'abc' str FROM DUAL)

)
SELECT vResult FROM sequence WHERE lSeq < 13

---

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

and if str = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' then:

SELECT vResult FROM sequence 
WHERE vResult in ('0001','0002','0009','000A','000Z',
                  '0010','0011','001A','ZZZZ')  --you get the idea...

I have found some questions at Stackoverflow that are fairly related. Base 36 to Base 10 conversion using SQL only and PL/SQL base conversion without functions. But with my current knowledge of SQL I am not quite able to hack this one...

---

EDITED:
Or well, it is almost like this one:

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 <= length(input_string)
  )
)

Excepted that I want to give the pos_val as a parameter and get the input_string out...

Answer 1

Here is one way. Another cool usage of the model function in Oracle SQL.