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.
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.