0

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Roger Gardner
  • 9
  • 1
  • Does this answer your question? [SQL Server converting varbinary to string](https://stackoverflow.com/questions/12139073/sql-server-converting-varbinary-to-string) – David Apr 07 '20 at 19:31

1 Answers1

1
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;
lptr
  • 1
  • 2
  • 6
  • 16