SQL hexadecimal string to decimal values

Question

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;

110100 is 52 not 54.

Thom A
– Thom A ♦

2021-02-24 15:26:12 +00:00
Commented Feb 24, 2021 at 15:26 — Thom A
– Thom A ♦, Commented Feb 24, 2021 at 15:26
My bad for the typo, just corrected it

Blue Owl
– Blue Owl

2021-02-24 15:32:05 +00:00
Commented Feb 24, 2021 at 15:32 — Blue Owl
– Blue Owl, Commented Feb 24, 2021 at 15:32

Thom A · Accepted Answer · 2021-02-24 15:35:17Z

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

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

Collectives™ on Stack Overflow

SQL hexadecimal string to decimal values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related