October 14, 2007 at 4:09 pm
Comments posted to this topic are about the item Converting Binary data to a Hex Character String
Gregory A. Larsen, MVP
October 28, 2008 at 10:55 am
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]
June 28, 2009 at 9:48 pm
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)
June 28, 2009 at 10:44 pm
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