OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to convert a number from any base to any base in SQL

April 19th, 2008 By Frank Zhou

The following SQL pattern can be used to convert a number in any base to any other base (Base <=16 ) in SQL.

variable input varchar2(28)
variable FromBase number
variable ToBase number
exec :FromBase:= 16
exec :ToBase:= 8
exec :input := '7B'


SELECT base_str_num
FROM
(SELECT SUM(num * POWER(FromBase, rn -1)) Base_10_input
 FROM
 (SELECT instr(num_str, upper(doc.extract('/X/text()').getStringVal()))-1 as num,
         rownum rn, FromBase
  FROM
  (SELECT '0123456789ABCDEF' as num_str, :FromBase as FromBase,:input as input FROM DUAL) a,
   TABLE(xmlSequence(extract(XMLType('<DOC>'||
   REGEXP_REPLACE(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(a.input)))
   ,'(.)','<X>\1</X>')||'</DOC>'),'/DOC/X'))) doc
 ))  
 MODEL
 DIMENSION BY (0 x)
 MEASURES (CAST(NULL AS varchar2(4000)) base_str_num, Base_10_input, :ToBase as ToBase)
 RULES ITERATE (3999) UNTIL (Base_10_input[0] = 0)
 (base_str_num[0] = substr('0123456789ABCDEF',mod(Base_10_input[cv()],ToBase[cv()])+1,1)||base_str_num[cv()],
  Base_10_input[0]= trunc(Base_10_input[cv()] / ToBase[cv()])
 );

BASE_STR_NUM
-----------------------
173


exec :FromBase:= 8
exec :ToBase:= 2
exec :input := '173'

SQL> /

BASE_STR_NUM
------------------------
1111011


exec :FromBase:= 16
exec :ToBase:= 5
exec :input := '10'

SQL> /

BASE_STR_NUM
------------------------------
31

4 Responses to “How to convert a number from any base to any base in SQL”

  1. DP1234 Says:

    Here is a nice elegant solution using plain SQL that was posted over on StackOverflow for converting bases:

    http://stackoverflow.com/questions/2568668/base-36-to-base-10-conversion-using-sql-only

  2. newkid Says:
    VAR STR VARCHAR2(10);
    EXEC :STR := '1234WXYZ';
    
    VAR FROM_BASE NUMBER;
    EXEC :FROM_BASE :=36;
    
    VAR TO_BASE NUMBER;
    EXEC :TO_BASE :=16;
    
              
    WITH n1 AS (
      SELECT SUM((CASE WHEN c BETWEEN '0' AND '9' THEN TO_NUMBER(c) 
                       ELSE ASCII(c)-ASCII('A')+10 
                  END)*POWER(:FROM_BASE,len-rn)    
                 ) AS the_num     
        FROM (SELECT SUBSTR(:STR,ROWNUM,1) c  
                    ,LENGTH(:STR) len         
                    ,ROWNUM rn                
                FROM DUAL 
              CONNECT BY ROWNUM<=LENGTH(:STR)
              )
    )
    ,n2 AS (
      SELECT (CASE WHEN n <10 THEN TO_CHAR(n) ELSE CHR(ASCII('A')+n-10) END) AS digi   
            ,rn
        FROM (SELECT MOD(TRUNC(the_num/POWER(:TO_BASE,ROWNUM -1)),:TO_BASE) n   
                    ,ROWNUM rn        
                FROM n1
              CONNECT BY ROWNUM <= TRUNC(LOG(:TO_BASE,the_num))+1  
             )
    )
    SELECT REPLACE(SYS_CONNECT_BY_PATH(digi,'*'),'*') AS result  
      FROM n2
    WHERE rn=1    
    START WITH rn = (SELECT MAX(rn) FROM n2)  
    CONNECT BY rn = PRIOR rn -1;
    
    
    RESULT
    ------------
    134DAA43FB
    
    
    11GR2:
    WITH n1(the_num,lvl,c) AS ( 
      SELECT 0,1,SUBSTR(:STR,1,1) FROM DUAL
      UNION ALL
      SELECT the_num*:FROM_BASE  
             +(CASE WHEN c BETWEEN '0' AND '9' THEN TO_NUMBER(c) 
                    ELSE ASCII(c)-ASCII('A')+10 
               END)
            ,lvl+1
            ,SUBSTR(:STR,lvl+1,1)
        FROM n1
       WHERE lvl<=LENGTH(:STR)
      )
    ,n2(the_num,n,result) AS ( 
      SELECT TRUNC(the_num/:TO_BASE),MOD(the_num,:TO_BASE),'' 
        FROM n1 
       WHERE lvl=LENGTH(:STR)+1
      UNION ALL
      SELECT (CASE WHEN the_num=0 THEN -1   
                   ELSE TRUNC(the_num/:TO_BASE) 
              END)
            ,MOD(the_num,:TO_BASE)   
            ,(CASE WHEN n =0
      )
    SELECT result FROM n2 WHERE the_num<0
    ;
    
    RESULT
    ----------------
    134DAA43FB
    
    
  3. newkid Says:
    VAR STR VARCHAR2(10);
    EXEC :STR := '1234WXYZ';
    
    VAR FROM_BASE NUMBER;
    EXEC :FROM_BASE :=36;
    
    VAR TO_BASE NUMBER;
    EXEC :TO_BASE :=16;
    
              
    WITH n1 AS (
      SELECT SUM((CASE WHEN c BETWEEN '0' AND '9' THEN TO_NUMBER(c) 
                       ELSE ASCII(c)-ASCII('A')+10 
                  END)*POWER(:FROM_BASE,len-rn)    
                 ) AS the_num     
        FROM (SELECT SUBSTR(:STR,ROWNUM,1) c  
                    ,LENGTH(:STR) len         
                    ,ROWNUM rn                
                FROM DUAL 
              CONNECT BY ROWNUM<=LENGTH(:STR)
              )
    )
    ,n2 AS (
      SELECT (CASE WHEN n <10 THEN TO_CHAR(n) ELSE CHR(ASCII('A')+n-10) END) AS digi   
            ,rn
        FROM (SELECT MOD(TRUNC(the_num/POWER(:TO_BASE,ROWNUM -1)),:TO_BASE) n   
                    ,ROWNUM rn        
                FROM n1
              CONNECT BY ROWNUM <= TRUNC(LOG(:TO_BASE,the_num))+1  
             )
    )
    SELECT REPLACE(SYS_CONNECT_BY_PATH(digi,'*'),'*') AS result  
      FROM n2
    WHERE rn=1    
    START WITH rn = (SELECT MAX(rn) FROM n2)  
    CONNECT BY rn = PRIOR rn -1;
    
    
    RESULT
    ------------
    134DAA43FB
    
    
    11GR2:
    WITH n1(the_num,lvl,c) AS ( 
      SELECT 0,1,SUBSTR(:STR,1,1) FROM DUAL
      UNION ALL
      SELECT the_num*:FROM_BASE  
             +(CASE WHEN c BETWEEN '0' AND '9' THEN TO_NUMBER(c) 
                    ELSE ASCII(c)-ASCII('A')+10 
               END)
            ,lvl+1
            ,SUBSTR(:STR,lvl+1,1)
        FROM n1
       WHERE lvl<=LENGTH(:STR)
      )
    ,n2(the_num,n,result) AS ( 
      SELECT TRUNC(the_num/:TO_BASE),MOD(the_num,:TO_BASE),'' 
        FROM n1 
       WHERE lvl=LENGTH(:STR)+1
      UNION ALL
      SELECT (CASE WHEN the_num=0 THEN -1   
                   ELSE TRUNC(the_num/:TO_BASE) 
              END)
            ,MOD(the_num,:TO_BASE)   
            ,(CASE WHEN n =0
      )
    SELECT result FROM n2 WHERE the_num<0
    ;
    
    RESULT
    ----------------
    134DAA43FB
    
    
    
  4. newkid Says:
    VAR STR VARCHAR2(10);
    EXEC :STR := '1234WXYZ';
    
    VAR FROM_BASE NUMBER;
    EXEC :FROM_BASE :=36;
    
    VAR TO_BASE NUMBER;
    EXEC :TO_BASE :=16;
    
    WITH n1 AS (
      SELECT SUM((CASE WHEN c BETWEEN '0' AND '9' THEN TO_NUMBER(c) 
                       ELSE ASCII(c)-ASCII('A')+10 
                  END)*POWER(:FROM_BASE,len-rn)    
                 ) AS the_num     
        FROM (SELECT SUBSTR(:STR,ROWNUM,1) c  
                    ,LENGTH(:STR) len         
                    ,ROWNUM rn                
                FROM DUAL 
              CONNECT BY ROWNUM<=LENGTH(:STR)
              )
    )
    ,n2 AS (
      SELECT (CASE WHEN n <10 THEN TO_CHAR(n) ELSE CHR(ASCII('A')+n-10) END) AS digi   
            ,rn
        FROM (SELECT MOD(TRUNC(the_num/POWER(:TO_BASE,ROWNUM -1)),:TO_BASE) n   
                    ,ROWNUM rn        
                FROM n1
              CONNECT BY ROWNUM <= TRUNC(LOG(:TO_BASE,the_num))+1  
             )
    )
    SELECT REPLACE(SYS_CONNECT_BY_PATH(digi,'*'),'*') AS result  
      FROM n2
    WHERE rn=1    
    START WITH rn = (SELECT MAX(rn) FROM n2)  
    CONNECT BY rn = PRIOR rn -1;
    
    
    
    WITH n1(the_num,lvl,c) AS ( 
      SELECT 0,1,SUBSTR(:STR,1,1) FROM DUAL
      UNION ALL
      SELECT the_num*:FROM_BASE  
             +(CASE WHEN c BETWEEN '0' AND '9' THEN TO_NUMBER(c) 
                    ELSE ASCII(c)-ASCII('A')+10 
               END)
            ,lvl+1
            ,SUBSTR(:STR,lvl+1,1)
        FROM n1
       WHERE lvl<=LENGTH(:STR)
      )
    ,n2(the_num,n,result) AS ( 
      SELECT TRUNC(the_num/:TO_BASE),MOD(the_num,:TO_BASE),'' 
        FROM n1 
       WHERE lvl=LENGTH(:STR)+1
      UNION ALL
      SELECT (CASE WHEN the_num=0 THEN -1   
                   ELSE TRUNC(the_num/:TO_BASE) 
              END)
            ,MOD(the_num,:TO_BASE)   
            ,(CASE WHEN n <10 THEN TO_CHAR(n) ELSE CHR(ASCII('A')+n-10) END)||result  
        FROM n2
       WHERE the_num>=0
      )
    SELECT result FROM n2 WHERE the_num<0
    ;
    
    

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question