Converting binary to text

Hey,

Another 'must be simple' question.. I'm wishing to convert a binary
field into a textual representation of that binary data. i.e. come out
with what the query analyser would display, and not try to convert the
binary into ascii/whatever encoded text.

Cheers for any clues,
ChrisNot Me wrote:
> Hey,
> Another 'must be simple' question.. I'm wishing to convert a binary
> field into a textual representation of that binary data. i.e. come out
> with what the query analyser would display, and not try to convert the
> binary into ascii/whatever encoded text.

Sorted now, through the master.dbo.fn_varbintohexstr function... took an
unnatural amount of google-ing tho :)

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

Convert int to date

Hi,
i have a string representation like '200509'. I want to convert that to
"Septembre 2005". So i've split the two parts up with the left and right
function (now i've got 09 and 2005). Is there a way to say that the 09-part
must transform automatically to "Septembre". I'd rather not use and endless
iif structure. Setting the textbox to a Date type doesn't seem to work
either.
Thx and kind regards,
KoenYou might be able to do something with the SQL functions DATEPART and
DATENAME, I'll look into it and get back to you.
Koen wrote:
>Hi,
>i have a string representation like '200509'. I want to convert that to
>"Septembre 2005". So i've split the two parts up with the left and right
>function (now i've got 09 and 2005). Is there a way to say that the 09-part
>must transform automatically to "Septembre". I'd rather not use and endless
>iif structure. Setting the textbox to a Date type doesn't seem to work
>either.
>Thx and kind regards,
>Koen
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Alternatively, you could write a function to convert it using a switch case
statement in custom code.
I'll write it for you if you like, I don't have much to do at the moment!
Tabby Cool wrote:
>You might be able to do something with the SQL functions DATEPART and
>DATENAME, I'll look into it and get back to you.
>>Hi,
>[quoted text clipped - 7 lines]
>>Thx and kind regards,
>>Koen
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Do you need all of the month names to be in French?
I'll do them that way and you can always change it later if you want
something else.
Koen wrote:
>Hi,
>i have a string representation like '200509'. I want to convert that to
>"Septembre 2005". So i've split the two parts up with the left and right
>function (now i've got 09 and 2005). Is there a way to say that the 09-part
>must transform automatically to "Septembre". I'd rather not use and endless
>iif structure. Setting the textbox to a Date type doesn't seem to work
>either.
>Thx and kind regards,
>Koen
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Are all of your dates 21st century dates? Or do you have some from last
century?
Tabby Cool wrote:
>Do you need all of the month names to be in French?
>I'll do them that way and you can always change it later if you want
>something else.
>>Hi,
>[quoted text clipped - 7 lines]
>>Thx and kind regards,
>>Koen
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Is the value you are using actually a string or an int?
The title of the thread would imply it's an integer, but you said in your
post that it's a string.
I've nearly finished your function, but I need to know whether it's a string
or an integer.
Tabby Cool wrote:
>Do you need all of the month names to be in French?
>I'll do them that way and you can always change it later if you want
>something else.
>>Hi,
>[quoted text clipped - 7 lines]
>>Thx and kind regards,
>>Koen
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1