0

I am trying to convert an hexadecimal string into decimal values, as follows:

Input : 24 character string

'1234567890abcdef12345678'

I need to extract the second to fourth characters, converting them into bits then separate them into two packs of 6 digits, and eventually convert them into decimal as follows:

'234'

bit conversion : 1000110100

separation into two packs of 6 digits : 001000, 110100

conversion into decimal : 8, 52

apply an operation : 8 * 2 - 128 = -112, 52 * 2 - 128 = -24

I have set up two functions to manage the conversion of each item (they also depend on another variable, which is a firmware, that has no influence in this case):

ALTER FUNCTION [getval1](@Firmware varchar(30), @RawData varchar(24)) RETURNS smallint AS BEGIN DECLARE @val1 smallint; IF @Firmware = 'v1' OR @Firmware = 'v1.1' OR @Firmware = 'v1.2' OR @Firmware = 'v2' BEGIN SET @val1 = (CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 1, 4)) AS int) % 4096 / 64) * 2 - 128; END ELSE BEGIN IF @Firmware = 'v3' OR @Firmware = 'v3.1' OR @Firmware = 'v3.2' OR @Firmware = 'v3.3' BEGIN SET @val1 = (CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 1, 4)) AS int) % 4096 / 64) * 2 - 128; END ELSE BEGIN SET @val1 = 0 END END RETURN @val1; END; ALTER FUNCTION [getval2](@Firmware varchar(30), @RawData varchar(24)) RETURNS smallint AS BEGIN DECLARE @val2 smallint; IF @Firmware = 'v1' OR @Firmware = 'v1.1' OR @Firmware = 'v1.2' OR @Firmware = 'v2' BEGIN SET @val2 = (CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 1, 4)) AS int) % 64) * 2 - 128; END ELSE BEGIN IF @Firmware = 'v3' OR @Firmware = 'v3.1' OR @Firmware = 'v3.2' OR @Firmware = 'v3.3' BEGIN SET @val2 = (CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 1, 4)) AS int) % 64) * 2 - 128; END ELSE BEGIN SET @val2 = 0; END END RETURN @val2; END;

However, if I run my functions with RawData = '5921000000b1212800b1219a', my outputs are :

  • The first function seems to work perfectly well (output is -56).
  • The second function outputs -126 instead of -62.

The strange thing to me is that if I just run the line:

SET @val2 = (CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 1, 4)) AS int) % 64)

I get the appropriate output (-62), but I don't get why the function returns a different output.

Any idea of what is happening here?

Many thanks!

EDIT : I forgot to mention that function fct.hexstrtovarbin returns a varbinary result from a string hexadecimal. The code is below:

CREATE FUNCTION fct.hexstrtovarbin(@input varchar(8000)) RETURNS varbinary(8000) AS BEGIN DECLARE @Result AS varbinary(8000) IF LEN(@input) % 2 <> 0 BEGIN SET @Result = 0000000000; END ELSE BEGIN SET @Result = CONVERT(VARBINARY(8000), @input, 2); END RETURN @Result END;
Improve this question
2
  • 110100 is 52 not 54. Commented Feb 24, 2021 at 15:26
  • My bad for the typo, just corrected it Commented Feb 24, 2021 at 15:32

1 Answer 1

Reset to default
2

Getting the 2nd to 4th characters of your string, is quite simple, you can just use SUBSTRING:

SELECT SUBSTRING('1234567890abcdef12345678',2,3);

NExt we want to convert the value to a binary value. SQL Server doesn't natively support binary numbers, however, it's quite easy to achieve with bitwise logic. As we have a fixed length binary we want, we can convert the value above into a varbinary, followed then an int, and then use bitwise logic to create the binary value. Extending what we had before, we get something like this:

SELECT STRING_AGG(CASE V.I & POWER(2,P.I) WHEN 0 THEN 0 ELSE 1 END,'') WITHIN GROUP (ORDER BY P.I) FROM (VALUES(SUBSTRING('1234567890abcdef12345678',2,3)))SS(Hex) CROSS APPLY (VALUES(CONVERT(int,CONVERT(varbinary(3),'0x0'+SS.Hex))))V(I) CROSS APPLY (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))P(I);

Now we have the binary value, we can split that value into two 6 digit values, and reverse the logic we had to get the relevant numbers:

WITH BinaryVal AS( SELECT STRING_AGG(CASE V.I & POWER(2,P.I) WHEN 0 THEN 0 ELSE 1 END,'') WITHIN GROUP (ORDER BY P.I DESC) AS BinaryNum FROM (VALUES(SUBSTRING('1234567890abcdef12345678',2,3)))SS(Hex) CROSS APPLY (VALUES(CONVERT(int,CONVERT(varbinary(2),'0x0'+SS.Hex,1))))V(I) CROSS APPLY (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))P(I)) SELECT SUM(CASE SUBSTRING(B1,P.I,1) WHEN 1 THEN POWER(2,P.P) END), SUM(CASE SUBSTRING(B2,P.I,1) WHEN 1 THEN POWER(2,P.P) END) FROM BinaryVal BV CROSS APPLY(VALUES(SUBSTRING(BV.BinaryNum,1,6),SUBSTRING(BV.BinaryNum,7,6)))B(B1, B2) CROSS APPLY(VALUES(6,0),(5,1),(4,2),(3,3),(2,4),(1,5))P(I,P);

This gives us 8 and 54 respectively. Then, finally, we can apply your *2 - 12 logic (which though not required I am going to add parenthesis to for clarity), which gives the -112 and -24 (-24 is correct as you state in the question 110100 is 54, however, it is 52):

WITH BinaryVal AS( SELECT STRING_AGG(CASE V.I & POWER(2,P.I) WHEN 0 THEN 0 ELSE 1 END,'') WITHIN GROUP (ORDER BY P.I DESC) AS BinaryNum FROM (VALUES(SUBSTRING('1234567890abcdef12345678',2,3)))SS(Hex) CROSS APPLY (VALUES(CONVERT(int,CONVERT(varbinary(2),'0x0'+SS.Hex,1))))V(I) CROSS APPLY (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))P(I)) SELECT (SUM(CASE SUBSTRING(B1,P.I,1) WHEN 1 THEN POWER(2,P.P) END) * 2) - 128, (SUM(CASE SUBSTRING(B2,P.I,1) WHEN 1 THEN POWER(2,P.P) END) * 2) - 128 FROM BinaryVal BV CROSS APPLY(VALUES(SUBSTRING(BV.BinaryNum,1,6),SUBSTRING(BV.BinaryNum,7,6)))B(B1, B2) CROSS APPLY(VALUES(6,0),(5,1),(4,2),(3,3),(2,4),(1,5))P(I,P);

We can test this with your other value, and this works correct as well, returning -56 and -62:

WITH BinaryVal AS( SELECT STRING_AGG(CASE V.I & POWER(2,P.I) WHEN 0 THEN 0 ELSE 1 END,'') WITHIN GROUP (ORDER BY P.I DESC) AS BinaryNum FROM (VALUES(SUBSTRING('5921000000b1212800b1219a',2,3)))SS(Hex) CROSS APPLY (VALUES(CONVERT(int,CONVERT(varbinary(2),'0x0'+SS.Hex,1))))V(I) CROSS APPLY (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))P(I)) SELECT (SUM(CASE SUBSTRING(B1,P.I,1) WHEN 1 THEN POWER(2,P.P) END) * 2) - 128, (SUM(CASE SUBSTRING(B2,P.I,1) WHEN 1 THEN POWER(2,P.P) END) * 2) - 128 FROM BinaryVal BV CROSS APPLY(VALUES(SUBSTRING(BV.BinaryNum,1,6),SUBSTRING(BV.BinaryNum,7,6)))B(B1, B2) CROSS APPLY(VALUES(6,0),(5,1),(4,2),(3,3),(2,4),(1,5))P(I,P);

db<>fiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Works perfectly, both within and out from a function, thank you! However it still does not tell me why my function returns a different result from the isolated line
Without the definition of findit.hexstrtovarbin it was impossible for me to say, @BlueOwl , so i just did the work myself.
Sorry about this, I was just editing the post as I figured this out as well

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.