当然，DB2中没有这样一个内置函数可以执行这种转换，但是有一个blog post showing how to define such a function。

--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION HEX2INT(str VARCHAR(8))
RETURNS INTEGER
SPECIFIC HEX2INT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res INTEGER  DEFAULT 0;
  DECLARE pos INTEGER DEFAULT 1;
  DECLARE nibble CHAR(1);
  WHILE pos <= LENGTH(str) DO
    SET nibble = SUBSTR(str, pos, 1);
    SET res = BITOR(CASE WHEN BITAND(res, 134217728) != 0
                         THEN BITOR(16 * BITANDNOT(res, 134217728),
                                    -2147483648)
                         ELSE 16 * res END,
                    CASE nibble
                         WHEN '0' THEN 0
                         WHEN '1' THEN 1
                         WHEN '2' THEN 2
                         WHEN '3' THEN 3
                         WHEN '4' THEN 4
                         WHEN '5' THEN 5
                         WHEN '6' THEN 6
                         WHEN '7' THEN 7
                         WHEN '8' THEN 8
                         WHEN '9' THEN 9
                         WHEN 'A' THEN 10
                         WHEN 'a' THEN 10
                         WHEN 'B' THEN 11
                         WHEN 'b' THEN 11
                         WHEN 'C' THEN 12
                         WHEN 'c' THEN 12
                         WHEN 'D' THEN 13
                         WHEN 'd' THEN 13
                         WHEN 'E' THEN 14
                         WHEN 'e' THEN 14
                         WHEN 'F' THEN 15
                         WHEN 'f' THEN 15
                         ELSE RAISE_ERROR('78000', 'Not a hex string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;

这里描述了用于各种转换操作的更多函数。

我相信您可以简化以下内容

WITH fred (x) AS (VALUES 'f1'), 

     nurk (a) as (SELECT UPPER(substr(x,1)) from fred 
                  union all 
                  select UPPER(substr(a,2)) from nurk 
                   where substr(a,1,1) <> ' '), 

     bare (b, c) as (select substr(a,1,1), (length(a) - 1) 
                  from nurk), 

     trap (d) as ((SELECT (ASCII(B) - ASCII('7')) * 
                   power(16,c) 
                   FROM BARE 
                   WHERE (B BETWEEN 'A' AND 'F') 
                   and 
                   c <> -1) 
                   union 
                  (SELECT (ASCII(B) - ASCII('0')) * 
                   power(16,c) 
                   FROM BARE 
                   WHERE (B not BETWEEN 'A' AND 'F') 
                   and 
                   c <> -1)) 
select sum(d) from trap

运行为
db2 -f“上述文件名”
得出结果
241
尝试使用f1以外的值进行测试
约翰·亨尼西

在标准SQL中（我希望如此）：

with inp (val) as                                                
(values ('FF'), ('AB'), ('ABCDEF')),                             
     calc(val, urval, res, f) as                                 
(select case when length(val) > 1                                
                  then substr(val, 1, length(val)-1)             
             else '' end, val,                                   
        locate(right(val, 1),'0123456789ABCDEF')-1, 16           
 from   inp                                                      
 union  all                                                      
 select case when length(val) > 1                                
                  then substr(val, 1, length(val)-1)             
             else '' end, urval,                                 
        res + ((locate(right(val, 1),'0123456789ABCDEF')-1) * f),
        f * 16                                                   
 from   calc                                                     
 where  length(val) > 0)                                         
select  urval, res 
from    calc                                     
where   val = ''

==〉
URVAL保留
法国法郎255
AB 171
美国广播公司电话号码11.259.375

convert( datatype, column_Name)

例如：convert(decimal(18,2), column_Name)--此处column_Name具有十六进制值

语法如下所示

select CONVERT(int, 0xFFFFFF)

如果需要有关与特定数据类型之间相互转换的详细信息，请查看下面的文档

数据类型

https://technet.microsoft.com/en-us/library/ms187594(v=sql.105).aspx

转换数据类型

https://technet.microsoft.com/en-us/library/ms191530(v=sql.105).aspx