Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Oracle: All versions FAQ

Tips and Tricks

How do I convert any integer (in any base) to any other base by SantaMufasa
Posted: 8 Apr 04

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor "Dasages, LLC" makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM Please contact the author via email (dave@dasages.com) when
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM [Please keep the above disclaimer and the embedded electronic
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM NAME: BASECONV.SQL - PL/SQL code to translate positive or
REM       negative integer values from any base (radix)
REM       (base 36 max.) to its counterpart value in any other
REM       base (base 36 max.).
REM         Co-principal, Dasages, LLC
REM         1-801-733-5333
REM **************************************************************
REM Usage:
REM       BASECONV (<value>,<source-base>,<target-base>)
REM   ...where <value> is some "number" using valid "numerals" in
REM   the <source-base>. For example, in base 2, valid numerals are
REM   "0" and "1"; in base 36, valid numerals are "0" thru "z".
REM   When using bases 11 thru 36, you specify alpha characters for
REM   numerals beyond decimal '9'. Alpha characters may be in either
REM   upper or lower case.
REM   ...where <source-base> is any base (radix) between 2 and 36.
REM   ...where <target-base> is any base (radix) between 2 and 36.
REM   Be sure to specify both source and target bases in base 10.
REM Sample invocations:
REM   col x heading "Conversion" format a20
REM   select baseconv(255,10,2) x from dual;
REM   Conversion
REM   ----------
REM   11111111
REM   select baseconv('Zebra',36,10) x from dual;
REM   Conversion
REM   ----------
REM   59454982
REM   select baseconv('aaaaaaaaaaaaaaaa',16,2) x from dual;
REM   Conversion
REM   ----------------------------------------------------------------
REM   1010101010101010101010101010101010101010101010101010101010101010
REM **************************************************************
Create or replace function BaseConv
    ( ValueIn    in varchar2    -- incoming value to convert
    , RadFrom    in number    -- source base
    , RadOut    in number    -- target base
    return         varchar2    -- outgoing value in target base
    ValIn        varchar2(1000);
    Sign        char;
    LenIn        number;
    Base10Value    number;
    DigitPool    varchar2(50)    := '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DigitHold    varchar(1);
    HighPower    number;
    CurrValue    number;
    CurrDigit    number;
    ResultingValue    varchar(2000);
    function GetDigit10 (InDigit in varchar2, RadIn in number) return number
        bad_digit    exception;
        pragma exception_init(bad_digit,-6502);
        if InDigit = '0' then
            return 0;
        end if;
        DigitHold := upper(InDigit);
        for i in 1..RadIn-1 loop
            if DigitHold = substr(DigitPool,i,1) then
                return i;
            end if;
        end loop;
        raise_application_error(-20000,'Illegal digit, "'||InDigit||'" for base "'||RadIn||'"');
    ValIn    :=    ValueIn;
    if substr(ValIn,1,1) = '-' then
        Sign    := '-';
        ValIn    := substr(ValIn,2);
        Sign    := null;
    end if;
    LenIn := length(nvl(ValIn,'0'));
    Base10Value    := 0;
    for i in 1..LenIn loop
        Base10Value    := Base10Value +
            GetDigit10(substr(ValIn,i,1),RadFrom) * power(RadFrom,LenIn-i);
    end loop;
    for i in 1..1000 loop
        if power(RadOut,i) > Base10Value then
            HighPower := i-1;
        end if;
    end loop;
    CurrValue    := Base10Value;
    ResultingValue    := null;
    for i in 0..HighPower loop
        CurrDigit := floor(Currvalue / power(RadOut,HighPower-i));
        CurrValue := Currvalue - (CurrDigit * power(RadOut,HighPower-i));
        if CurrDigit = 0 then
            ResultingValue := ResultingValue||'0';
            ResultingValue := ResultingValue||substr(DigitPool,CurrDigit,1);
        end if;
    end loop;
    return sign||ResultingValue;

Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close