CODEWITHSUNDEEP
sql-serverfilebinaryattachment
FNG
FNG

Reputation: 185

Convert non-hex text in varchar(max) to varbinary

I have a table in SQL Server, with binary data stored as a string in a varchar(max) field.

The table name is attachment, and the field is named "documentbody".

select id, mimetype, documentbody
from attachment

The files stored in the table are mostly PDF, but also include JPG and PNG and probably some other file types too.

Here is a sample of what one of the "files" looks like, when queried (first 100 characters only):

JVBERi0xLjQKJeLjz9MNCjEgMCBvYmoKPDwvVHlwZSAvUGFnZQovUGFyZW50IDIgMCBSCi9NZWRpYUJveCBbIDAgMCA2MTIuMDAw

How can I convert this data into actual binary data?

screenshot of query results

Upvotes: 0

Views: 355

Answers (1)

Dale K
Dale K

Reputation: 27364

When one wishes to convert data from one datatype to another, and no implicit convert exists, one uses either CAST or CONVERT.

e.g.

select cast(MyColumn as varbinary(max)), convert(varbinary(max), MyColumn)
from MyTable;

CAST is ANSI-SQL for what it is worth whereas CONVERT is SQL Server specific. However CONVERT handles many other cases including specific formatting, which CAST doesn't handle.

OK, taking a total guess here, many people encode binary data as base64 so try this:

SELECT CAST(CAST(N'' AS XML).value('xs:base64Binary(sql:column("MyColumn"))', 'VARBINARY(MAX)') AS VARCHAR(MAX))
FROM MyTable;

Upvotes: 1

Related Questions