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...

I'm not sure I would necessarily consider an invocation of such kind of pl/sql function a performance mistake. Indeed, you will suffer from some context switching, but you're not hiding anything from the query optimizer (as you would if your function itself performed some additional selects, for example), thus not preventing it from building an effective execution plan, which is usually 90% of your performance. SQL-only solution, on the other hand, might cause trouble here. — Kirill Leontev, Sep 07 '13 at 06:05

score -1 · Answer 1 · answered Sep 07 '13 at 04:04

-1

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

answered Sep 07 '13 at 04:04

DCookie

42,630
11
83
92

ORA-00900: invalid SQL statement – wittrup Sep 07 '13 at 11:28
looks like this link is no longer valid. – antony.trupe Aug 16 '16 at 20:36

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

1 Answers1