SQL Server string to varbinary conversion

Question

Ok, the problem is that there's a merger or join that needs to be done on 2 tables. One has file content stored as an [image] type or varbinary(max), the other has the file content stored as a hex string. if I upload the same content into both tables

the content as string (bytearray to string) would look like like this...

'application/vnd.xfdl;content-encoding="base64-gzip"
H4sIAAAAAAAAC+y9e1fjONI4/H9/Cg173idwFgIJl+5m6MzPJAayE+KsnXQPs8+cHJMY8HZi57ET
aObMh3918UW2Jcdyrmbg7E7HtqpUpSqVSqWSdPHLj/EIPBuOa9rWl51K+WgHGNbAHprW45edpqYc
fPp0+vmgsvNL7cPFb1eNFoDlLffLztN0Ojk/PHx5eSl3Zo4hDx+N8sAeH6Iyh2fl0x1S8Hwwc6f2'    
...

the content as image looks like (and this is ultimately what I want it to look like)

0x6170706C69636174696F6E

if I do select convert(varbinary(MAX), @contentAsString) I get 0x6100700070006C00690063006100740069006F006E

it appears as though the conversion is on target but putting two zeros (00) between each, I'll call it a byte for lack of better words.

I've tried all sorts of more complicated methods posted across forums but to no avail. Any help would be appreciated.

In your example the other table contains base64 encoded and not hex string. Is the example what you intend to receive as output or is it the actual source format? — Kasper van den Berg, Nov 25 '16 at 08:45

score 33 · Answer 1 · edited Nov 13 '16 at 20:29

33

From MSDN

In SQL Server 2008, these conversions are even more easier since we added support directly in the CONVERT built-in function. The code samples below show how to perform the conversion(s):
declare @hexstring varchar(max);

set @hexstring = '0xabcedf012439';

select CONVERT(varbinary(max), @hexstring, 1);

set @hexstring = 'abcedf012439';

select CONVERT(varbinary(max), @hexstring, 2);

go

declare @hexbin varbinary(max);

set @hexbin = 0xabcedf012439;

select
   CONVERT(varchar(max), @hexbin, 1),
   CONVERT(varchar(max), @hexbin, 2);

go

edited Nov 13 '16 at 20:29

Dai

141,631
28
261
374

answered Jan 03 '13 at 21:04

Woot4Moo

23,987
16
94
151

The @hexstring looks like this: `'application/vnd.xfdl;content-encoding="base64-gzip" H4sIAAAAAAAAC+y9e1fjONI4/H9/Cg173idwFgIJl+5m6MzPJAayE+KsnXQPs8+cHJMY8HZi57ET'...` target binary = `0x6170706C69636174696F6E` `select CONVERT(varbinary(max), @hexstring);` - succeeded, but wrong binary 0x6100700070006C00690063006100740069006F006E `select CONVERT(varbinary(max), @hexstring, 1);` - failed "Error converting data type nvarchar to varbinary" `select CONVERT(varbinary(max), @hexstring, 2);` - failed "Error converting data type nvarchar to varbinary" – Matthew Weir Jan 04 '13 at 16:02
I've tested almost everything I can find online such as "From MSDN" type posts. The question remaining is, why does the generic CONVERT(varbinary(MAX), @hexstring) pad every 2 characters with 00 and is there a way to get rid of it? – Matthew Weir Jan 04 '13 at 16:19

score 5 · Accepted Answer · edited Mar 06 '13 at 09:44

Ok, so the padded 00 has been answered.

DECLARE @hexStringNVar nvarchar(max)
DECLARE @hexStringVAR varchar(max)

SET @hexStringNVar = '{my hex string as described above}'
SET @hexStringVAR = '{my hex string as described above}'

select CONVERT(varbinary(MAX), @hexStringNVar)) = 0x6100700070006C00690063...
select CONVERT(varbinary(MAX), @hexStringVAR)) = 0x6170706C6963...

The 00 padding is because of Unicode or NVARCHAR as opposed to VARCHAR.

So, since the stored data is in nvarchar(max), the solution is this:

select CAST(cast(@hexStringNVar as varchar(max)) as varbinary(max)) = 0x6170706C6963...

I'm sure that convert would work just as well but my target SQL Server is 2005.

or change the storage type to `varchar` instead of it being `nvarchar` — GoldBishop, Apr 29 '17 at 14:57

SQL Server string to varbinary conversion

2 Answers2

Linked