CODEWITHSUNDEEP
databaseoracle-databasehex
Matthew Farwell
Matthew Farwell

Reputation: 61695

Oracle: How do I convert hex to decimal in Oracle SQL?

How do I convert hexadecimal to decimal (and back again) using Oracle SQL?

Upvotes: 30

Views: 115216

Answers (6)

Asif Jamil
Asif Jamil

Reputation: 1

    CREATE OR REPLACE FUNCTION HEXTODEC (p_hexadecimal IN varchar2 default '0') RETURN varchar2
IS
v__power INTEGER := 0;
v__hex_number INTEGER;
v__length NUMBER;
v__place_value VARCHAR2(100);
v__multiply NUMBER;
v__decimal number := 0;
v__decimalEEEE LONG;
BEGIN
SELECT LENGTH(UPPER(p_hexadecimal))
INTO v__length
FROM dual;
FOR i IN 1..v__length
LOOP
SELECT POWER(16,v__power)
INTO v__hex_number
FROM dual;
v__power := v__power + 1;

    FOR j IN 1..v__length LOOP
        CONTINUE WHEN i > j;
      IF i = j THEN
    SELECT DECODE(SUBSTR(SUBSTR(UPPER(p_hexadecimal), -j,j),1,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',16 ,NULL)
    INTO v__place_value
    FROM DUAL;
    v__multiply := v__place_value * v__hex_number;        
    v__decimal := NVL(v__multiply,0)+ NVL(v__decimal,0);
    END IF;
    END LOOP;
END LOOP;
        RETURN (v__decimal);
 
EXCEPTION
WHEN OTHERS THEN
    SELECT TO_CHAR(v__decimal,'9.9EEEE')
    INTO v__decimalEEEE
    FROM DUAL;
    RETURN v__decimalEEEE;
END;
/

Upvotes: 0

Jared Still
Jared Still

Reputation: 146

Even though the 'X' option of to_number and to_char have been available for a very long time, they still don't seem to appear in Oracle documentation.

In addition to converting to and from hexadecimal, it is sometimes desirable to convert to and from binary or even octal. Oracle does not provide anything for that that I am aware of.

I found this thread while searching to see if Oracle has yet added a built in method to convert to/from binary, but it seems that still is not available

Quite some time ago I wrote a PL/SQL package 'Radix' that can be used to convert between various formats.

The following demo SQL:

select radix.to_hex(285) from dual;
select radix.to_dec('11D', 16) from dual;
select radix.to_bin(255) from dual;
select radix.to_bin(256) from dual;

Produced these results:

RADIX.TO_HEX(285)
----------------------------------------
11D

RADIX.TO_DEC('11D',16)
----------------------
                   285

RADIX.TO_BIN(255)
----------------------------------------
11111111

RADIX.TO_BIN(256)
----------------------------------------
100000000

The code is open source (MIT License) , and available here if interested:

Oracle Radix Package

Upvotes: 1

Petros
Petros

Reputation: 8992

If you are using 8.1.5 and above you can use:

To convert from hexadecimal to decimal:

select to_number('AA', 'xx') from dual;

To convert from decimal to hexadecimal:

select to_char(111, 'xxxx') from dual;

Upvotes: 45

devio
devio

Reputation: 37205

Starting in Oracle8i, the TO_CHAR and TO_NUMBER functions can handle conversions from base 10 (decimal) to base 16 (hexadecimal) and back again:

SQL> select to_char(123,'XX') to_hex, 
2    to_number('7B','XX') from_hex   
3   from dual  
4  /   
TO_     FROM_HEX
------  -----------------
7B     123

source

Upvotes: 6

Quassnoi
Quassnoi

Reputation: 425341

SELECT  TO_NUMBER('DEADBEEF', 'XXXXXXXX')
FROM    dual

---
3735928559

SELECT  TO_CHAR(3735928559, 'XXXXXXXX')
FROM    dual
---
 DEADBEEF

Upvotes: 13

dassouki
dassouki

Reputation: 6366

FTA: Oracle to Decimal :

CREATE OR REPLACE FUNCTION hex2dec (hexnum IN CHAR) RETURN NUMBER IS
  i                 NUMBER;
  digits            NUMBER;
  result            NUMBER := 0;
  current_digit     CHAR(1);
  current_digit_dec NUMBER;
BEGIN
  digits := LENGTH(hexnum);
  FOR i IN 1..digits LOOP
     current_digit := SUBSTR(hexnum, i, 1);
     IF current_digit IN ('A','B','C','D','E','F') THEN
        current_digit_dec := ASCII(current_digit) - ASCII('A') + 10;
     ELSE
        current_digit_dec := TO_NUMBER(current_digit);
     END IF;
     result := (result * 16) + current_digit_dec;
  END LOOP;
  RETURN result;
END hex2dec;
/
show errors

CREATE OR REPLACE FUNCTION num2hex (N IN NUMBER) RETURN VARCHAR2 IS
  H  VARCHAR2(64) :='';
  N2 INTEGER      := N;
BEGIN
  LOOP
     SELECT RAWTOHEX(CHR(N2))||H
     INTO   H
     FROM   dual;

     N2 := TRUNC(N2 / 256);
     EXIT WHEN N2=0;
  END LOOP;
  RETURN H;
END num2hex;
/
show errors

Upvotes: 1

Related Questions