Converting a HEX string to its binary representation

I am trying to take a hexadecimal representation of a binary number and convert to the true binary representation so that I can compare it against a binary field in one of my tables.

After reading the documentation it seems I should be able to do this with the CAST or CONVERT function. However it does not appear to be working correctly.

Can you tell me why this T-SQL code produces the wrong binary value:

DECLARE @.value binary(16)

SELECT @.value = CONVERT(binary, '0764DE49749F274EB924E1552FFE09EC')

PRINT @.value

This prints out: 0x30373634444534393734394632373445

That is not correct it should be: 0x0764DE49749F274EB924E1552FFE09EC

Thanks

Chris:

If you are really only dealing with conversion of a constant what you want to do instead of

SELECT @.value = CONVERT(binary, '0764DE49749F274EB924E1552FFE09EC')

is

SELECT @.value = CONVERT(binary, 0x0764DE49749F274EB924E1552FFE09EC)

Dave

|||

You might also be able to use directly:

SELECT @.value = 0x0764DE49749F274EB924E1552FFE09EC

|||

Thanks but my example is just that an example to demonstrate the problem.

The real issues is that I am recieving the binary values as hexadecimal through xml. I need to convert these to their binary representations so that I can query for them. Here is a more complete example:

ALTER PROCEDURE dbo.spc_rels_byRelIds
(
@.ItemsXML TEXT
)
AS

DECLARE @.hDoc int

EXEC sp_xml_preparedocument @.hDoc output, @.ItemsXML

SELECT *
FROM tblRelAttrVals
INNER JOIN
(
SELECT
CONVERT(binary, relID) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)
) XmlItems
ON grel = relID

EXEC sp_xml_removedocument @.hDoc

Where grel is binary(16) column in the tblRelAttrVals table.

|||

Chris:

Look at this example from earlier this year:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=386406&SiteID=1

|||

Ok so I have this as an example of a stored proc:

if @.hexStr like '%[^abcdef0123456789]%'
BEGIN
set @.b = NULL
return @.b
END

DECLARE @.sql nvarchar(100)
DECLARE @.parms nvarchar(100)

SET @.sql = N'select @.out=0x' + @.hexStr
SET @.parms = N'@.out varbinary(30) output'
EXEC sp_executesql @.sql, @.parms, @.out = @.b output

The problem is I really need a function so that I can do this:

SELECT
dbo.HexToBin(relID) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

But functions won't let you call exec sp_executesql from within them.

Does anybody see a way around this?

|||

You didn't mention the version of SQL Server you are using. Below are few optimized solutions that doesn't require dynamic SQL. Use the scalar UDF solution only if you have less number of rows in the table and performance is not important for you. Otherwise, the scalar UDF approach will perform poorly than say inlining the expression in the UDF directly in the SELECT statement.

-- Scalar UDF for SQL Server 2000:

create function hexstr2bin4 (@.hexstr char(8))
returns bigint
with schemabinding
as
begin
return (
((charindex(lower(substring(@.hexstr, 1, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 7))
+ ((charindex(lower(substring(@.hexstr, 2, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 6))
+ ((charindex(lower(substring(@.hexstr, 3, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 5))
+ ((charindex(lower(substring(@.hexstr, 4, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 4))
+ ((charindex(lower(substring(@.hexstr, 5, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 3))
+ ((charindex(lower(substring(@.hexstr, 6, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 2))
+ ((charindex(lower(substring(@.hexstr, 7, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 1))
+ ((charindex(lower(substring(@.hexstr, 8, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 0))
)
end
go

-- Use it like below:

SELECT
dbo.hexstr2bin4(relID) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

-- Inline TVF which provides the best performance for using in SELECT statements

-- But this will work only in SQL Server 2005.

create function hexstr2bin4 (@.hexstr char(8))
returns table
as
return (
select ((charindex(lower(substring(@.hexstr, 1, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 7))
+ ((charindex(lower(substring(@.hexstr, 2, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 6))
+ ((charindex(lower(substring(@.hexstr, 3, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 5))
+ ((charindex(lower(substring(@.hexstr, 4, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 4))
+ ((charindex(lower(substring(@.hexstr, 5, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 3))
+ ((charindex(lower(substring(@.hexstr, 6, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 2))
+ ((charindex(lower(substring(@.hexstr, 7, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 1))
+ ((charindex(lower(substring(@.hexstr, 8, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 0)) as bin4
)

go

-- Use it like below:

SELECT
h.bin4 as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

CROSS APPLY dbo.hexstr2bin4(relID) as h

-- or

SELECT
(SELECT h.bin4 FROM dbo.hexstr2bin4(relID) as h) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

|||Thank you very much. I have to support SQL 2000 and SQL 2005. How is the performance for a 16 byte binary value? The SQL 2000 method seems a bit heavy handed, especially being run within a select statement.|||

The performance of the scalar UDF depends on the number of rows on which you are running the SELECT statement to perform the conversion. You need to compare the scalar UDF against the inline approach for your dataset / queries and see. For SQL Server 2005, you can use the expression below in an inline TVF to get the best performance.

Also, below is another way to convert the string to binary. You can extend it to support any arbitrary length upto 8000 bytes.

create function hexstr2bin (@.hexstr varchar(32))

returns binary(32)

as

begin

return

cast((charindex(lower(substring(@.hexstr, 2, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 1, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 4, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 3, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 6, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 5, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 8, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 7, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 10, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 9, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 12, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 11, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 14, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 13, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 16, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 15, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 18, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 17, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 20, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 19, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 22, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 21, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 24, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 23, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 26, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 25, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 28, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 27, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 30, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 29, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 32, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 31, 1)), '0123456789abcdef')-1)*16) as binary(1))
end