Convert HEXADECIMAL values to INTEGER values

Hello Experts,

How to convert Hexadecimal values to Integer values?

Suppose data coming from source system is:

And user wants this data to convert into Integer values.

Output should be:

Experimentation Time 🙂 :

Below is the mapping:

Query mapping:

Logic behind the custom function CF_HEX_TO_INT :

###    $p_string (varchar(255)) will take the Hexadecimal values and will return the integer values 
####   $lv_length (int) will evaluate the length of Hexadecimal value                               
###    $lv_count (int) will always be 1 whenever it encounters a new row                            
###    $lv_count value is incremented by 1 inside while loop                                        
###    $lv_sum (int), Initial sum is set to 0                                                       
###    $lv_char (varchar(1)) will fetch the Hexadecimal value one by one from $p_string             
####   $lv_num is the integer alue corresponding to Hexadecimal value                               

print( 'Value entering function is: '|| $p_string );
$p_string = upper( $p_string );
$lv_length = length( $p_string );
print( 'Length of string is: '|| $lv_length );
$lv_count = 1;
$lv_sum = 0;
while ( $lv_count <= $lv_length  )
	begin
$lv_char = substr( $p_string, -1*($lv_count) , 1 );
print( 'Hexadecimal number is: '|| $lv_char ); 		
if ( $lv_char = ('A') )
begin
	$lv_num = 10;
	$lv_num = $lv_num*power( 16, $lv_count-1 ); 
	print( 'Integer value of [$lv_char] is: '|| $lv_num );
end
else if ( $lv_char = ('B') )
begin
	$lv_num = 11;
	$lv_num = $lv_num*power( 16, $lv_count-1 ); 
	print( 'Integer value of [$lv_char] is: '|| $lv_num );	
end
else if ( $lv_char = ('C') )
begin
	$lv_num = 12;
	$lv_num = $lv_num*power( 16, $lv_count-1 ); 
	print( 'Integer value of [$lv_char] is: '|| $lv_num );	
end
else if ( $lv_char = ('D') )
begin
	$lv_num = 13;
	$lv_num = $lv_num*power( 16, $lv_count-1 ); 
	print( 'Integer value of [$lv_char] is: '|| $lv_num );	
end
else if ( $lv_char = ('E') )
begin
	$lv_num = 14;
	$lv_num = $lv_num*power( 16, $lv_count-1 ); 
	print( 'Integer value of [$lv_char] is: '|| $lv_num );	
end
else if ( $lv_char = ('F') )
begin
	$lv_num = 15;
	$lv_num = $lv_num*power( 16, $lv_count-1 ); 
	print( 'Integer value of [$lv_char] is: '|| $lv_num );
end
else if ( $lv_char in (0,1,2,3,4,5,6,7,8,9))	
begin
    $lv_num = $lv_char ;
	$lv_num = $lv_num*power( 16, $lv_count-1 ); 
	print( 'Integer value of [$lv_char] is: '|| $lv_num );	
end
else if ( $lv_char not in (0,1,2,3,4,5,6,7,8,9,'A','B','C','D','E','F'))	
begin
    $lv_num = 0;
	print( 'This is not Hexadecimal number: '|| $lv_char );	
end
    $lv_sum = $lv_num + $lv_sum ;
	print( 'Sum is: '|| $lv_sum );
	$lv_count = $lv_count + 1;
	end
$p_string = $lv_sum ;
print( 'Integer is: '|| $p_string );

Return $p_string;


###           Return value of $p_string is converted into int                          ######

Sorry the code is pretty long!

Processing: 

This function will take the Hexadecimal value from source in $p_string variable. This value will go through a loop. Using substr function it’ll extract each number/character one by one. If it’s a Hexadecimal number then it’ll pass through if then else condition and accordingly that number will be first converted to integer. If the number/character isn’t a Hex then directly it’ll jump to

“else if ( $lv_char in (0,1,2,3,4,5,6,7,8,9))”.

$lv_sum = $lv_num + $lv_sum ; will keep on adding the integer values untill the loop condition terminates to false.

Finally, value of $lv_num will be assigned to $p_string, which will be returned.

Hope it helps you to understand the processing behind every row.

Thanks! 🙂

Let me know if I’ve missed anything!