RE: Base conversion

From: Kevin Lange <klange_at_ppoone.com>
Date: Tue, 28 Jan 2003 08:19:32 -0800
Message-ID: <F001.0053C252.20030128081932@fatcity.com>

Just happen to have this lying around....

CREATE OR REPLACE function
base_x2y
  (
    input varchar2,
    basein integer,
    baseout integer
  )
Return varchar2 is output varchar2(255);

  output_val integer := 0;

  char_val varchar2(1) := null;
  number_val integer := 0;

  input_size  integer       := 0;
  pos         integer       := 0;

begin
  select length(input) into input_size from dual;   pos := 1;
  while pos <= input_size
  loop
    select decode(substr(input,pos,1),'0', 0,'1', 1,'2', 2,'3', 3,'4', 4,'5', 5,

                                      '6', 6,'7', 7,'8', 8,'9',

9,'A',10,'B',11,  

'C',12,'D',13,'E',14,'F',15,'G',16,'H',17,
 

'I',18,'J',19,'K',20,'L',21,'M',22,'N',23,
 

'O',24,'P',25,'Q',26,'R',27,'S',28,'T',29,
 

'U',30,'V',31,'W',32,'X',33,'Y',34,'Z',35, 0) into number_val from dual;

    select (output_val + number_val * power(basein,(input_size-pos))) into output_val from dual;

    pos := pos + 1;
  end loop;
  while output_val > 0
  loop
    number_val := baseout * ((output_val/baseout) - trunc(output_val/baseout));

    output_val := trunc(output_val/baseout);

    select decode(number_val, 0,'0', 1,'1', 2,'2', 3,'3', 4,'4', 5,'5',
                              6,'6', 7,'7', 8,'8', 9,'9',10,'A',11,'B',
                             12,'C',13,'D',14,'E',15,'F',16,'G',17,'H',
                             18,'I',19,'J',20,'K',21,'L',22,'M',23,'N',
                             24,'O',25,'P',26,'Q',27,'R',28,'S',29,'T',
                             30,'U',31,'V',32,'W',33,'X',34,'Y',35,'Z',' ')

into char_val from dual;

    output := char_val||output;
  end loop;
  return output;
end;
/

-----Original Message-----
Sent: Tuesday, January 28, 2003 9:45 AM
To: Multiple recipients of list ORACLE-L

For very obscure reasons (read: one of those developer decisions that you want to use a time-machine to go back and change), we're storing some information in base-36 (0,1,2,3...8,9,A,B,C,...,Y,Z) in a varchar field. And you thought hexadecimal was fun :-)

Now some bright spark would like me to build some PL/SQL to do base conversion - in the first instance from base 36 to base 10 (i.e decimal). Has anyone done something similar in the past ... that I could borrow or co-opt? As you've guessed, the deadline is yesterday :-)

Ciao
Fuzzy
:-)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Grant Allen
  INET: grant_at_towersoft.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kevin Lange
  INET: klange_at_ppoone.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jan 28 2003 - 10:19:32 CST