Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting Binary data to a Hex Character String Expand / Collapse
Author
Message
Posted Sunday, October 14, 2007 4:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
Comments posted to this topic are about the item Converting Binary data to a Hex Character String

Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #410658
Posted Tuesday, October 28, 2008 10:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 16, 2014 7:41 AM
Points: 102, Visits: 206
Thanks for the script. The script shows 11 as 'D', it should be 'B'

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


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




Post #593099
Posted Sunday, June 28, 2009 9:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:53 PM
Points: 2, Visits: 21
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)
Post #743336
Posted Sunday, June 28, 2009 10:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:53 PM
Points: 2, Visits: 21
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.
Post #743346
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse