SQL byte truncate

My input is a varchar payload (that is actually made of hexadecimal characters). I would like to convert it to bits, then truncate the result to decode it. I already have a built-in function (hexstrtovarbin) that works correctly and that converts varchars into varbinaries.

For example, for an input "4d", I would like to convert it into bits (01001101) then truncate the first 6 digits, before converting them into an integer (and get eventually 19).

DECLARE @payload varchar(4), @binarypayload binary(1), @converted smallint;

SET @payload = '4d';
SET @binarypayload = hexstrtovarbin(@payload);
SET @converted = CAST(SUBSTRING(@binarypayload, 1, 6) AS int)

If I proceed like this, @converted takes 77 as value. This is because @binarypayload value is "TQ==" (and not actual bits), so the substring does not truncate it.

I have tried to use bit data type, but could not store more than 1 of them.

Would anyone know how to get the actual bits in order to truncate them?

Solution

it can be messy but this is my answer, according to your data in the question:

declare @intvalue int
set @intvalue=  CONVERT(int, CONVERT(varbinary(max), '4d', 2) )

declare @vsresult varchar(16)
declare @inti int
select @inti = 16, @vsresult = ''
declare @Input varchar(16)

--translating ex string in binary digits

while @inti>0
  begin
    select @vsresult=convert(char(1), @intvalue % 2)+@vsresult
    select @intvalue = convert(int, (@intvalue / 2)), @inti=@inti-1
  end


set @Input= left(@vsresult,LEN(@vsresult)-2)  -- here your input string without last two digits,   00000000010011 


--now return integer value
 DECLARE @Cnt tinyint = 1
    DECLARE @Len tinyint = LEN(@Input)
    DECLARE @Output bigint = CAST(SUBSTRING(@Input, @Len, 1) AS bigint)

    WHILE(@Cnt < @Len) BEGIN
        SET @Output = @Output + POWER(CAST(SUBSTRING(@Input, @Len - @Cnt, 1) * 2 AS bigint), @Cnt)

        SET @Cnt = @Cnt + 1
    END

select @Output