asked
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 ... DECLARE @bin VARBINARY(64) DECLARE @bin2 VARBINARY(64) DECLARE @char VARCHAR(64) SET @bin = 0x020000008A596A10BA403E299E0BF0EE0D22976C53F6AA06 SELECT CONVERT(VARCHAR(64), @bin, 2) 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 DECLARE @Table TABLE ( Bin_Values VARCHAR(64) ) INSERT INTO @Table ( 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?
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 DECLARE @Table TABLE ( Bin_Values VARCHAR(64) ) INSERT INTO @Table ( Bin_Values ) VALUES ( '020000008A596A10BA403E299E0BF0EE0D22976C53F6AA06' ), ( '02000000EE634318A5EB0A72DD794F2223EC572324253446' ) SELECT [t].[Bin_Values] , CONVERT(VARBINARY(64), [t].[Bin_Values], 2) FROM @Table AS t
So, despite all my data having the '0x' start characters I have to use an alternate style!?
@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
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
