database Oracle：如何在 Oracle SQL 中将十六进制转换为十进制？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1227039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: How do I convert hex to decimal in Oracle SQL?
提问by Matthew Farwell
How do I convert hexadecimal to decimal (and back again) using Oracle SQL?
如何使用 Oracle SQL 将十六进制转换为十进制（然后再转换回来）？
回答by Petros
If you are using 8.1.5 and above you can use:
如果您使用的是 8.1.5 及更高版本，则可以使用：
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;
回答by Quassnoi
SELECT TO_NUMBER('DEADBEEF', 'XXXXXXXX')
FROM dual
---
3735928559
SELECT TO_CHAR(3735928559, 'XXXXXXXX')
FROM dual
---
DEADBEEF
回答by devio
Starting in Oracle8i, the TO_CHAR and TO_NUMBER functions can handle conversions from base 10 (decimal) to base 16 (hexadecimal) and back again:
从 Oracle8i 开始，TO_CHAR 和 TO_NUMBER 函数可以处理从基数 10（十进制）到基数 16（十六进制）再返回的转换：
SQL> select to_char(123,'XX') to_hex,
2 to_number('7B','XX') from_hex
3 from dual
4 /
TO_ FROM_HEX
------ -----------------
7B 123
回答by dassouki
FTA: Oracle to Decimal:
FTA：甲骨文到十进制：
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
