Working with the BINARY datatype

I am banging my head against a problem that I am sure shouldnt be as tricky as I am making it feel ...
I have an XML source that I am using XQuery to shred out values. One of these values is a BINARY value that is within the XML as a varchar. I am taking the 5 values into a table variable and then want to use the 'BINARY' column in a join. This works ...

SET @bin = 0x020000008A596A10BA403E299E0BF0EE0D22976C53F6AA06

SELECT  @char = CONVERT(VARCHAR(64), @bin, 2)

SET @bin2 = CONVERT(VARBINARY(64), @char, 2)

SELECT  @bin2

I am able to take a BINARY value, store it as a VARCHAR and then return it to a BINARY value without issue. However when I try to apply this to my 'live' work it errors.

This code section produces the same error

      Bin_Values VARCHAR(64)
        ( Bin_Values )
VALUES  ( '0x020000008A596A10BA403E299E0BF0EE0D22976C53F6AA06' ),
        ( '0x02000000EE634318A5EB0A72DD794F2223EC572324253446' )

SELECT  [t].[Bin_Values] ,
        CONVERT(VARBINARY(64), [t].[Bin_Values], 2)
FROM    @Table AS t

I cannot get past the error to varbinary.

Msg 8114, Level 16, State 5, Line 11
Error converting data type varchar to varbinary.
Can anyone help me out please?
more ▼

asked Jun 03 '11 at 04:35 AM in Default

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

Try style 1

SELECT  [t].[Bin_Values] ,
        CONVERT(VARBINARY(64), [t].[Bin_Values], 1)
FROM    @Table AS t

The difference between style 1 and 2 is that 1 requires the '0x' at the beginning, 2 doesn't.

So this is fine

      Bin_Values VARCHAR(64)
        ( Bin_Values )
VALUES  ( '020000008A596A10BA403E299E0BF0EE0D22976C53F6AA06' ),
        ( '02000000EE634318A5EB0A72DD794F2223EC572324253446' )

SELECT  [t].[Bin_Values]
       ,    CONVERT(VARBINARY(64), [t].[Bin_Values], 2)
FROM @Table AS t
more ▼

answered Jun 03 '11 at 05:06 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 49 76

So, despite all my data having the '0x' start characters I have to use an alternate style!?
Jun 03 '11 at 10:02 AM Fatherjack ♦♦
...or use CAST([t].[Bin_Values] as VARBINARY(64))
Jun 03 '11 at 04:24 PM Scot Hauder

@Scot Hauder - I tried CAST. Have you? It was doing this for me ...

Bin_Values                       Convert_Value                    Cast_Value
0x020000008A596A10BA403E299E0BF0EE0D22976C53F6AA06  0x020000008A596A10BA403E299E0BF0EE0D22976C53F6AA06   0x3078303230303030303038413539364131304241343033453239394530424630454530443232393736433533463641413036
0x02000000EE634318A5EB0A72DD794F2223EC572324253446 0x02000000EE634318A5EB0A72DD794F2223EC572324253446 0x3078303230303030303045453633343331384135454230413732444437393446323232334543353732333234323533343436
Jun 04 '11 at 01:29 AM Fatherjack ♦♦
got it, I copied Kev's solution..not the one with 0x..
Jun 04 '11 at 01:43 AM Scot Hauder
while we're on binary types, a big annoyance of mine is you cannot do bitwise operations on 2 binary types. That would seem obvious to me but one has to be a bigint/int. Stupid
Jun 04 '11 at 01:55 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 03 '11 at 04:35 AM

Seen: 1594 times

Last Updated: Jun 03 '11 at 04:37 AM