In my project, I have scenario where i need to convert the hexadecimal value to integer value,But there is no standard function to convert the value. After searching in SCN, finally got a suggestion from one of SCN friend’s(Rajan) then i could able to achieve the requirement by creating an optimized custom function.
So I just want to share my scenario here.
We are receiving input data from source as below:
Datatype of Rowversion is ‘timestamp’, so I’m unable to import this column from source. hence I decided to check ‘import unsupported datatype as VARCHAR of size’ option at data store level and then re-imported the table from source.
Now the datatype of Rowversion has been converted into Varchar(255) in BODS.
Our requirement of output data is:
Logic of the custom function:
$LV_VIN = upper($P_VIN) ; $LV_RESULT = DECODE(length(LTRIM(RTRIM($LV_VIN, ' '),' ')) > 0 , 0 , -1 ); $LEN = length(LTRIM(RTRIM($LV_VIN, ' '),' ')); #PRINT($LEN ); IF ($LV_RESULT =0) begin $LV_VINMod = $LV_VIN ; $LV_Pos =0; $LV_Value =0; WHILE ($LV_Pos <= $LEN ) BEGIN $lv_char = substr($LV_VINMod ,$LEN-$LV_Pos , 1 ); #print('position'||$lv_char); $LV_Value = $LV_Value + (cast(decode($lv_char = 'A' ,10, $lv_char = 'B' ,11, $lv_char = 'C' ,12, $lv_char = 'D' ,13, $lv_char = 'E' ,14, $lv_char = 'F' ,15, $lv_char),'INT') * power(16,cast($LV_Pos,'INT'))); $LV_Pos = $LV_Pos + 1; END END PRINT ($LV_Value); RETURN $LV_Value ;
This function will take the Query input and Firstly it will convert the input value into upper case to avoid the case issues. And then count the length of the input value; this will help out the iteration of loop. Take the help of substr function to check each digit, if it is (A,B,C,D,E,F) then will take corresponding value(10,11,12,13,14,15) otherwise calculated power with same digit.
Added sum value to $LV_Value until end of the iteration for each inputvalue.
Final will return the output value $LV_Value as int.
Hope this will help.