DB2下将16进制转为十进制

近期才接触DB2，由于项目要求，需要将表中十六进制字段转为十进制，查了好久只发现DB2有十进制转十六进制的函数HEX，却没有十六进制转十进制的，寻了好久还是没有比较满意的办法，于是自己动手搞了个，感觉还不错！

也总想着养成开始写博客的习惯，故此记录一下，作为写博客的开始吧：

CREATE FUNCTION PT.HEXTOINT
 ( S_STR VARCHAR(15) ) 
  RETURNS BIGINT
  SPECIFIC PT.SQL091011189988811
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  STATIC DISPATCH
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  INHERIT SPECIAL REGISTERS
  RETURN 
 with s as (select val,length(val) len from (select replace(replace(trim(S_STR),'0x',''),'0X','') val from sysibm.dual ) ),
T (val ,len ) as
(select val,len from s 
union all
select s.val,t.len-1  from s,t where t.val=s.val and t.len-1>0)
select sum(power(16,int(seq)-1)*int(str)) from (select rownumber() over(order by len desc ) seq , len,
case  upper(substr(val,len,1)) 
when 'A' then '10' 
when 'B' then '11'
when 'C' then '12'
when 'D' then '13'
when 'E' then '14'
when 'F' then '15'
else  upper(substr(val,len,1))  end
str from t  ) 
where trim(str)<>'0'
  ;