 # 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!

### Assigned Tags

You must be Logged on to comment or reply to a post. Nice post. I like the way of representation of this post. Actually, https://qanda.typicalstudent.org/ team has suggested me to read this. It is really awesome information. Blog Post Author

Hello David,

Thanks! Hi Rajan,

Thankyou for the explanation. i have made more easier than pretty long 🙂

``````\$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 ;``````  