OraQA

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

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.

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

DP1234 Says:
April 2nd, 2010 at 12:42 pm
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

newkid Says:
June 24th, 2010 at 9:44 am

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

newkid Says:
June 24th, 2010 at 9:45 am

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

newkid Says:
June 25th, 2010 at 6:37 am

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
;

You must be logged in to post a comment.

RSS feed for comments on this question

« How to solve the Stamp Combo Puzzle in SQL

How to solve the Change One Puzzle in SQL »

Oracle Question and Answer

Categories

Links

Oracle News

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

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

Leave a Reply