SAP Data Services: the ultimate hexadecimal to decimal conversion function

When it comes to DS performance, many factors can be important. The one with most impact often is the design of your jobs. This does not only apply to data flow design (see my blogs on SQL pushdown and ECC data extraction for some interesting examples), but also on how you write your custom functions. Remember that a custom function used in a column mapping will be executed for every record that streams thru your data flow. Saving just a couple of microseconds on a custom function call will help decreasing overall execution time, especially when you have to process several millions of records.
There’s been quite some communication recently on how to convert hexadecimal values to decimal numbers. I wasn’t too enthusiastic about some of the suboptimal code I have seen. So, in order to avoid you would blame lousy performance of your DS jobs on the product again, I thought I’d publish my own solution. It’s here for you to reuse. And please, let me know if you can do it better.
Here you go!

Specify:
Input parameter: $Hex varchar(8)
Return value decimal(20)

Define local variables:
$c varchar(1)
$i int
$i_0 int
$i_A int
$Str varchar(8)

Then write the function code as follows:

$Str = $Hex;
$c = substr($Str, 1, 1);
$i = 0;
$i_0 = ascii( ‘0’);
$i_A = ascii( ‘A’) – 10;
while ($c is not null)
begin
$i = $i * 16 + ascii($c) – decode($c < 'A', $i_0, $I_A);
$Str = substr ($Str, 2, 8);
$c = substr($Str, 1, 1);
end
return ($i);