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 binary to string

is there a way to query a varbinary column and print the readable char in query analyzer?

thanks in advance!

Ryan

No way to do this, because SQL only store the binary data and has no sense for character encoding and structure of the doc.|||

There is a way, here it is...

declare @.pass varchar(30)
declare @.binPass varbinary(8)
set @.binPass = (select password from tblEMP where empid = '123')
set @.pass = (select cast(@.binPass as varchar(20)))
print reverse(@.pass)

I get characters like squares after the text, but I know to disregard them and only look at the text. Anyone know how to get rid of the squares and weird characters? thanks

Ryan

|||

SQL Server will convert varbinary to varchar what it will not convert is varbinary to text. Try the link below for the complete conversion chart and code samples. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

sqlsql

Converting Binary Image to Readable Text

I have a table that contains the following two columns:

BITS (image(16))
BIT_LENGTH (int(4))

When I look at the table, I see "OLE Object" in the BITS column. What
syntax should I use in a SELECT statement to convert the binary image
info contained in "BITS" into simple text that I can read? What role
does the BIT_LENGTH field play?An image column can store any binary data, so you need to know what the
data represents before you can display it - it could be a Word
document, a PDF, an MP3 etc. Do you already know exactly what the
column is storing ('text' is the normal data type for large amounts of
text data)? As for BIT_LENGTH, it could mean anything, depending on the
data model - there's no need for an image column to have an associated
length column with it.

It sounds a little like you've just taken over a database from someone
else? If so, you might want to review this chapter from the SQL2000
Resource Kit, which provides more detailed information about storing
and retrieving BLOBs:

http://www.microsoft.com/technet/pr...art3/c1161.mspx

Simon

converting binary data to another data type

I have a client application written in C++ to takes an array of doubles and
stores it into a SQL Server 2000 database as an image data type.
We just upgraded to Visual Studio 2005 and SQL Server 2005.
Can the Reporting Services take this image data and convert it to an array
of doubles so that it can be displayed using Reporting Services?
Thanks,
GloriaGloria (Gloria@.discussions.microsoft.com) writes:
> I have a client application written in C++ to takes an array of doubles
> and stores it into a SQL Server 2000 database as an image data type.
> We just upgraded to Visual Studio 2005 and SQL Server 2005.
> Can the Reporting Services take this image data and convert it to an array
> of doubles so that it can be displayed using Reporting Services?
I don't know Reporting Services, so I canot answer the question with any
certainty, but my gut feeling is that you would have to call some piece
of code to unpack that array. Tip: there is a Reporting Services newsgroup,
microsoft.public.sqlserver.reportingsvcs.
The main reason I post, is that I can't refrain from making the comment
table design appears a bit unorthodox to me. Or to put it more bluntly, a
serious violation of first normal form since it includs a repearing
group. The normal way of storing the data would be have a subtable,
and store one float value on each row.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Converting a string of binary numbers to a binary datatype

I have a varchar field which holds numeric (binary) data e.g. '00101111' and I want to convert this to a binary data type with the value e.g. 0x00101111

But when I try the following SQL:

select top 5 flag2,convert(binary,flag2) flag2_as_binary from my_table

I get:

flag2 flag2_as_binary
--- -------------------
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000

(5 row(s) affected)

I want some SQL that will return the following (with flag2_as_binary as a real binary datatype):

flag2 flag2_as_binary
--- -------------------
00000000 0x00000000
00000000 0x00000000
00000000 0x00000000
00000100 0x00000100
00000100 0x00000100

(5 row(s) affected)

Thanks.The sql binary datatype is actually displayed hexidecimal base 16 usinge characters 0-9 and A-F, not base 2.

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

Converting a Hex string to binary

I am trying to write a function to convert a hex string to binary. I would like it in a function so I can use it on aggregate data in queries (instead of having to cursor through the data). So, I write my function:

CREATE FUNCTION HexToBinary (@.hexstring char(16)) RETURNS binary(8)
AS
BEGIN
declare @.b binary(8)
,@.sql nvarchar(255)

SET @.sql = N'SET @.b = 0x' + @.hexstring
EXEC sp_executesql @.sql,N'@.b binary(8) out',@.b output
RETURN @.b
END

Then, I try to call my function:

SELECT HexToBinary('E0')

...and I get:

Msg 195, Level 15, State 10, Line 1
'HexToBinary' is not a recognized built-in function name.

However, I can get it to work if I use a slightly different syntax:

declare @.b binary(8)
exec @.b = HexToBinary 'E0'
select @.b

Any thoughts as to what might be going on? Obviously, the lower syntax does not help me call this function in queries, which is really my goal.sp_executesql is indeterministic and is not allowed within a UDF.

Here is the UDF that would give you what you want (courtesy of Itzik):

create function dbo.fn_hexstrtovarbin(@.input varchar(8000))
returns varbinary(8000)
as
begin
declare @.result varbinary(8000), @.i int, @.l int

set @.result = 0x
set @.l = len(@.input)/2
set @.i = 2

while @.i <= @.l
begin
set @.result = @.result +
cast(cast(case lower(substring(@.input, @.i*2-1, 1))
when '0' then 0x00
when '1' then 0x10
when '2' then 0x20
when '3' then 0x30
when '4' then 0x40
when '5' then 0x50
when '6' then 0x60
when '7' then 0x70
when '8' then 0x80
when '9' then 0x90
when 'a' then 0xa0
when 'b' then 0xb0
when 'c' then 0xc0
when 'd' then 0xd0
when 'e' then 0xe0
when 'f' then 0xf0
end as tinyint) |
cast(case lower(substring(@.input, @.i*2, 1))
when '0' then 0x00
when '1' then 0x01
when '2' then 0x02
when '3' then 0x03
when '4' then 0x04
when '5' then 0x05
when '6' then 0x06
when '7' then 0x07
when '8' then 0x08
when '9' then 0x09
when 'a' then 0x0a
when 'b' then 0x0b
when 'c' then 0x0c
when 'd' then 0x0d
when 'e' then 0x0e
when 'f' then 0x0f
end as tinyint) as binary(1))
set @.i = @.i + 1
end

return @.result
end
go

Converting a binary/hexadecimal to Varchar

How should I convert a binary/hexadecimal value to Varchar
Thanks in Advance,
JakeOriginally posted by Jake K
How should I convert a binary/hexadecimal value to Varchar

Thanks in Advance,
Jake

select cast(Binaryvalue as varchar)sqlsql

Convert varchar(12) to binary(12)

I need help converting a varchar field to binary.

I have a Data Control Task that has a OLE DB Source and corresponding OLE DB Destination Data Flow Task. In the referenced source table there exists a field defined as a varchar(12), in the corresponding destination table it is defined as a binary(12). How do I perform this conversion?

I tried inserting a Data Conversion Task and assigning the new data type as byte stream[DT_BYTES] and a Length = 12, but this was a bust. Output test is as follows:

Error: 0xC020901C at DFT - Windows, OLE_SRC - Windows [1]: There was an error with output column "Payload" (40) on output "OLE DB Source Output" (12). The column status returned was: "The value could not be converted because of a potential loss of data.".

Can anyone help me out here?

Regards,

What is the SSIS data type assigned to the source column? Right-click on the data flow error and slect Edit and then go to the Metadata tab of the Data Flow Path Editor dialog.

Is it DT_WSTR or DT_STR?

My guess is that SSIS is treating your input field as a Unicode string (DT_WSTR) so each of the 12 characters is taking up 2 bytes.