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
– Kasper van den Berg, Commented Nov 25, 2016 at 8:45

Dai · Accepted Answer · 2016-11-13 20:29:57Z

35

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, 2016 at 20:29

Dai

157k31 gold badges314 silver badges436 bronze badges

answered Jan 3, 2013 at 21:04

Woot4Moo

24.4k19 gold badges103 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matthew Weir Over a year ago

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 Over a year ago

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?

Michał Powaga · Accepted Answer · 2013-03-06 09:44:49Z

6

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.

edited Mar 6, 2013 at 9:44

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

answered Jan 4, 2013 at 16:43

Matthew Weir

2141 gold badge2 silver badges9 bronze badges

1 Comment

GoldBishop Over a year ago

or change the storage type to varchar instead of it being nvarchar

Collectives™ on Stack Overflow

SQL Server string to varbinary conversion

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related