CODEWITHSUNDEEP
sqlsql-server
Roger Gardner
Roger Gardner

Reputation: 9

Getting 'text characters out of image field' SQL Server 2008

Help with COVID-19 data query

I work on a third party system SQL Server 2008 that bizarrely stores a vast amount of numbers in an image column.

These are actually data of a ventilator:

0x7D010015001500150015001600160015......7D010015001500150015001600160015

When I copy the column data and paste from the clipboard into the query window, I can do the necessary below.

7D01
0015
0015
0015
0015
0016
0016
0015

I can process the numbers using a table valued function quite well - convert from hex to decimal.

What I can't find is a means to treat the image data as literal text. I've tried:

CAST(CAST(Data as VARBINARY(MAX)) as VARCHAR(MAX))  etc.

If someone could help the NHS would be very grateful!

Upvotes: 0

Views: 279

Answers (1)

lptr
lptr

Reputation: 6808

declare @t table (i image default(cast(12345 as varbinary(20))));
insert into @t (i) values(default);

select *, convert(varchar(max), cast(i as varbinary(max)), 2)
from @t;

Upvotes: 1

Related Questions