Converting Binary data to a Hex Character String

  • Comments posted to this topic are about the item Converting Binary data to a Hex Character String

    Gregory A. Larsen, MVP

  • Thanks for the script. The script shows 11 as 'D', it should be 'B'

    (incorrect)

    [font="Courier New"]...

    when (@c)/power(16,1)%16 = 11 then 'D'

    ...

    when (@c)/power(16,0)%16 = 11 then 'D'[/font]

    should be

    [font="Courier New"]...

    when (@c)/power(16,1)%16 = 11 then 'B'

    ...

    when (@c)/power(16,0)%16 = 11 then 'B'[/font]

  • Agree, this script just saved me a heap of time, but noticed a little spelling mistake which i was going to report, but see I am not the only one to spot it.

    Also used similar logic for a function to display binary strings:

    CREATE function dbf_binary_to_binstring(

    @binary_input varbinary(64),

    @readable BIT = 0

    )

    RETURNS varchar(100)

    AS

    BEGIN

    declare @C INT

    declare @binnum char(255)

    DECLARE @binary_field varbinary(64)

    set @binnum = ''

    SET @binary_field = @binary_input

    while len(@binary_field) > 0

    BEGIN

    SET @C=cast(substring(@binary_field,1,1) as int)

    SET @binary_field=substring(@binary_field,2,len(@binary_field))

    SET @binnum = rtrim(@binnum) +

    case

    WHEN @readable = 1 THEN ' '

    ELSE ''

    END +

    case

    WHEN (@c)/power(2,7)%2 = 0 then '0'

    when (@c)/power(2,7)%2 = 1 then '1'

    END +

    case

    WHEN (@c)/power(2,6)%2 = 0 then '0'

    when (@c)/power(2,6)%2 = 1 then '1'

    END +

    case

    WHEN (@c)/power(2,5)%2 = 0 then '0'

    when (@c)/power(2,5)%2 = 1 then '1'

    END +

    case

    WHEN (@c)/power(2,4)%2 = 0 then '0'

    when (@c)/power(2,4)%2 = 1 then '1'

    END +

    case

    WHEN @readable = 1 THEN '-'

    ELSE ''

    END +

    case

    WHEN (@c)/power(2,3)%2 = 0 then '0'

    when (@c)/power(2,3)%2 = 1 then '1'

    END +

    case

    WHEN (@c)/power(2,2)%2 = 0 then '0'

    when (@c)/power(2,2)%2 = 1 then '1'

    END +

    case

    WHEN (@c)/power(2,1)%2 = 0 then '0'

    when (@c)/power(2,1)%2 = 1 then '1'

    END +

    case

    WHEN (@c)/power(2,0)%2 = 0 then '0'

    WHEN (@c)/power(2,0)%2 = 1 then '1'

    END

    END

    IF @readable = 1

    SET @binnum = rtrim(@binnum) + ' (0x' + rtrim(dbo.dbf_binary_to_hex(@binary_input)) + ')'

    RETURN @binnum

    END

    go

    -- Testing

    SELECT dbo.dbf_binary_to_binstring(0x1, 1)

    SELECT dbo.dbf_binary_to_binstring(0xAF, 0)

    SELECT dbo.dbf_binary_to_binstring(0xAF, 1)

  • Just found another quite serious problem. The two instances of "len" should be replaced by "datalength". Otherwise you can get problems with binary data with 0x20 in it. For instance 0x0E20 ends up being reported as 0x0E because the 20 is regarded as a space and so is not included in the "len" function results.

    Actually it's not just spaces that affect this it's probably any non character value. Using "datalength" fixes this issue. Note there are two places where len is used.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply