Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> converting to different bases

converting to different bases

From: Steve A <spectre_at_hkstar.com>
Date: Thu, 10 Feb 2000 17:01:34 +0800
Message-ID: <1e5spen.1w8ax7p6lmqwyN@[192.168.2.50]>


Hi,
on a recent hunt ot convert 1 base to another, I found this 'GEM' on the oracle best routines website.

unfortunatly Mr, ploeg appears to be a bit of a sloppy programmer/documentor, ( as is oracle for not testing it)

1.in that BASE2BASE(33,10,2,8), which should convert a decimal to a binary number 8 long, actually returns 0011. 2. BASE2BASE(32,10,2,8) actually returns 000100000 (9 digits) infact the routine always returns 1 more than the length you want returned.

would anybody care to fix these 2 bugs or improve the following routine.

one of the bugs that returns 0011, is actually de to the line:  IF p_length < 1 OR l_valin = 1 THEN

since l_valin = 1 evaluates to true as soon as the 32 has been dealt with, exiting the code and failing to insert '000'

Converting a Number from Any Base to Any Base

        This code comes from Jack Ploeg, a developer with Logica in Groningen, The Netherlands.

        This function converts a number from any base to any base (both between 2 and 36). You call it with 3

        parameters: the value to be converted, the base of this value and te desired base for the returned value.

        Example:

        SQL> select base2base('65af',16,10) from dual;

        BASE2BASE('65AF',16,10)
        --------------------------------------
        026031

        SQL> select base2base('xyz',36,2) from dual;

        BASE2BASE('XYZ',36,2)
        --------------------------------------
        01010101111111011 

         


        Script: CREATE OR REPLACE FUNCTION base2base( p_valin IN
VARCHAR2

, p_basein IN NUMBER
, p_baseout IN NUMBER
, p_length IN PLS_INTEGER
default null) RETURN VARCHAR2 is l_digitstr VARCHAR2(36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; l_length PLS_INTEGER; l_tmp NUMBER; l_valin NUMBER :=0; l_cvalin VARCHAR2(200) := UPPER(p_valin); l_pos PLS_INTEGER; BEGIN IF p_basein > 36 OR p_baseout > 36 OR p_basein < 2 OR p_baseout < 2 then RETURN NULL; ELSE IF p_basein <> 10 THEN -- convert to decimal, to enable standard calulation functions FOR t IN 1 .. LENGTH(l_cvalin) LOOP l_valin := l_valin * p_basein; l_pos := INSTR(l_digitstr, SUBSTR(l_cvalin,t,1)) -1; IF l_pos >= p_basein THEN RETURN NULL; ELSE l_valin := l_valin + l_pos; END IF; END LOOP; ELSE l_valin := p_valin; END IF; IF p_length < 1 OR l_valin = 1 THEN -- this is the last digit to be converted RETURN(SUBSTR(l_digitstr, l_valin + 1, 1)); ELSE -- calculate lengt needed if not passed as parameter IF p_length IS NOT NULL THEN l_length := p_length; ELSE l_length := CEIL(LOG(p_baseout, l_valin)); END IF; -- compute first digit, pass remainder to calculate rest of digits l_tmp := POWER(p_baseout, l_length); RETURN(SUBSTR(l_digitstr, FLOOR(l_valin / l_tmp) + 1, 1) ||base2base(MOD(l_valin,l_tmp), 10, p_baseout,l_length -1)); end if; end if; end;
Received on Thu Feb 10 2000 - 03:01:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US