SQL Server : files as binary text extract

Question

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.

Does this answer your question? Convert a BINARY stored as VARCHAR to BINARY — SMor
– SMor, Commented Sep 24, 2021 at 19:49
89504E470D0A1A0 .. is the header hex signature for png. select convert(varbinary(max), cast(cast('89504E470D0A1A0A00' as text) as varchar(max)), 2) — lptr
– lptr, Commented Sep 24, 2021 at 19:52
@SMor i tryed that but got error "Explicit conversion from data type text to binary is not allowed." do i need to convert it to varchar first? — babiloof
– babiloof, Commented Sep 24, 2021 at 20:08
@lptr hah, youre right, i just asumed it was jpg because another column had the filename with the ending .jpg in it... and with your query combined with the info on page SMor wrote above i was able to solve it, thank you guys :D — babiloof
– babiloof, Commented Sep 24, 2021 at 20:46

Dan Guzman · Accepted Answer · 2021-09-24 21:37:43Z

1

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;

edited Sep 24, 2021 at 21:37

answered Sep 24, 2021 at 20:46

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

SQL Server : files as binary text extract

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related