sql-servertype-conversionvarbinary

SQL Server : files as binary text extract

I have an old SQL Server database where there are files stored with the datatype text.

For example a .jpg image looks like this

89504E470D0A1A0A0000000D4948445200000A80000005F00806000000788B1B29000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000.........

I'm guessing that this is binary?

How can I extract these to files?

I've tried various methods for extracting but failed which I'm guessing is due to the datatype being text and not blob or varbinary.

Solution

  • Cast the text column as varchar(MAX) and convert to varbinary(MAX) with binary style 2:

    SELECT CONVERT(varbinary(MAX), CAST(YourTextColumn AS varchar(MAX)), 2)
FROM dbo.YourTable;