Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Decimal to hexadecimal conversion vith Oracle

Sylvain CROUET
Respected Contributor

Decimal to hexadecimal conversion vith Oracle

Hi!

Is there a way to convert decimal value to hexadecimal within an Oracle SQL request?
6 REPLIES
Nicolas Dumeige
Esteemed Contributor

Re: Decimal to hexadecimal conversion vith Oracle

Bonjour Sylvain,

If think there is no direct SQL function to do this.

Take a look at :

http://www.revealnet.com/pipelines/plsql/archives.htm#code27

Cheers

Nicolas
All different, all Unix
Indira Aramandla
Honored Contributor

Re: Decimal to hexadecimal conversion vith Oracle

Hi,

You can get the hexadecimal value for a number by using the function TO_CHAR in SQLPLUS.

To convert a decimal number to hexadecimal For example, the number "123" will be converted to "7B".

SQL> select to_char(123,'XXX') from dual

output : 7B

You must ensure that your format string is as large as the biggest value you will need to convert, but this is a built-in feature.

Or you can write a PLSQL package to define your own function for the conversion. Eg:- below.

FUNCTION dec_to_hex (decin IN NUMBER) RETURN VARCHAR2 IS
v_decin NUMBER;
v_next_digit NUMBER;
v_result varchar(2000);
BEGIN
v_decin := decin;
WHILE v_decin > 0 LOOP
v_next_digit := mod(v_decin,16);
IF v_next_digit > 9 THEN
IF v_next_digit = 10 THEN v_result := 'A' || v_result;
ELSIF v_next_digit = 11 THEN v_result := 'B' || v_result;
ELSIF v_next_digit = 12 THEN v_result := 'C' || v_result;
ELSIF v_next_digit = 13 THEN v_result := 'D' || v_result;
ELSIF v_next_digit = 14 THEN v_result := 'E' || v_result;
ELSIF v_next_digit = 15 THEN v_result := 'F' || v_result;
ELSE raise_application_error(-20600,'Untrapped exception');
END IF;
ELSE
v_result := to_char(v_next_digit) || v_result;
END IF;
v_decin := floor(v_decin / 16);
END LOOP;
RETURN v_result;
END dec_to_hex;

v_return := v_return + TO_NUMBER(v_charval) * POWER(2,v_power);


I hope this helps.

Indira A

Never give up, Keep Trying
Sanjay Kumar Suri
Honored Contributor

Re: Decimal to hexadecimal conversion vith Oracle

Oracle 9i has a function rawtohex() which returns the raw string x converted to hexadecimal as the following example shows:

SQL> select rawtohex(job_id) from jobs;

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Yogeeraj_1
Honored Contributor

Re: Decimal to hexadecimal conversion vith Oracle

hi,

In 8i (8.1) and up, it is simply:
select to_char( number, 'xxxxxxx' ) from t;


hope this helps too!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sylvain CROUET
Respected Contributor

Re: Decimal to hexadecimal conversion vith Oracle

Well, this Oracle function seem not to work in my case:

SQL> select ip_address, to_char(ip_address, 'XXXXX') from opc_node_names where node_name like 'cexi01%';

IP_ADDRESS TO_CHA
---------- ------
957054740 ######


SQL> select ip_address, rawtohex(ip_address) from opc_node_names where node_name like 'cexi01%';

IP_ADDRESS RAWTOHEX(IP_ADDRESS)
---------- --------------------------------------------
957054740 C50A3A063029


IP_ADDRESS's type is NUMBER(12)
Nicolas Dumeige
Esteemed Contributor

Re: Decimal to hexadecimal conversion vith Oracle

Does it work with on X for every figure ?

select to_char(957054740,'XXXXXXXXX') from dual;

TO_CHAR(95
----------
390B7F14

Cheers

Nicolas
All different, all Unix