From Oracle FAQ
Jump to: navigation, search

Hecidecimal (hex) is a numbering system with 16 digits: 0 to 9 and A to F.

Conversion functions[edit]

Use the following conversion PL/SQL functions to convert hexadecimal (hex) values to decimal and vice versa:

CREATE OR REPLACE FUNCTION hex2dec (hexval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
  digits := length(hexval);
  for i in 1..digits loop
     current_digit := SUBSTR(hexval, i, 1);
     if current_digit in ('A','B','C','D','E','F') then
        current_digit_dec := ascii(current_digit) - ascii('A') + 10;
        current_digit_dec := to_number(current_digit);
     end if;
     result := (result * 16) + current_digit_dec;
  end loop;
  return result;
END hex2dec;

SQL> SELECT hex2dec('AFB2') FROM dual;
CREATE OR REPLACE FUNCTION dec2hex (N in number) RETURN varchar2 IS
  hexval varchar2(64);
  N2     number := N;
  digit  number;
  hexdigit  char;
  while ( N2 > 0 ) loop
     digit := mod(N2, 16);
     if digit > 9 then 
       hexdigit := chr(ascii('A') + digit - 10);
       hexdigit := to_char(digit);
     end if;
     hexval := hexdigit || hexval;
     N2 := trunc( N2 / 16 );
  end loop;
  return hexval;
END dec2hex;

SQL> SELECT dec2hex(44978)  FROM dual;

Also see[edit]

  • Binary - numbering system with 2 digits
  • Octal - numbering system with 8 digits
Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #