Oracle 19中如何将varchar(19)类型十六进制值转换为十进制

数据库环境：Oracle 19

转换失败基本是存储类型不匹配、转换函数参数错误导致的，以下是覆盖绝大多数场景的可落地实现：

• 存储字段为VARCHAR2/RAW类型，内容为标准十六进制串
  直接使用内置TO_NUMBER函数，指定十六进制格式掩码即可，注意掩码的X数量要匹配字段存储的十六进制串最大长度（最多支持16个X，对应64位无符号整数），示例代码：

  -- 替换表名、字段名为实际业务中的名称
  SELECT TO_NUMBER(hex_column, 'XXXXXXXXXXXXXXXX') AS decimal_result
  FROM target_table;
  

  该写法默认兼容大小写混合的十六进制字符，无需额外做大小写转换。
• 存储字段为BLOB类型，内容长度不超过2000字节
  先通过内置包将BLOB截取转换为字符串，再执行数值转换，示例代码：

  SELECT TO_NUMBER(
           UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_column, 2000, 1)),
           'XXXXXXXXXXXXXXXX'
         ) AS decimal_result
  FROM target_table;
  

• 存储的十六进制串带0x前缀、前后空格或冗余补位字符
  先做字符串清洗再执行转换，示例代码：

  SELECT TO_NUMBER(
           REGEXP_REPLACE(TRIM(hex_column), '^0x', ''),
           'XXXXXXXXXXXXXXXX'
         ) AS decimal_result
  FROM target_table;
  

排错参考：如果以上语句执行仍报错，先执行SELECT DUMP(hex_column, 16) FROM target_table查看字段实际存储的字节内容，确认不存在非十六进制字符（合法范围为0-9、A-F、a-f），如果存在不可见特殊字符，先用REPLACE函数剔除对应字符后再转换。如果待转换的十六进制串长度超过16位，会超出Oracle NUMBER类型的整数精度范围，需要自定义大数逐位计算函数，不要直接用TO_NUMBER转换避免精度丢失。

内容的提问来源于stack exchange，提问作者siva kumar