How to handle SQL Server IMAGE data type as TEXT data type

Trang Bartell

Hello, I'm learning to migrate an old application that has tables with coumns defined as IMAGE data type which were used to store text comments. I need help with the following, please:

Either:

1. How to convert the data from IMAGE data type to TEXT data type

Or:

2. How to retrieve the data from IMAGE data type and display as Text and Update/Insert back into the database.

Thanks in advance for your help.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Trang Bartell5y ago
Thank you for your response...however the data stored in this column is not an Image. It's a compressed text with value like the sample below.

0x789C1DC6410EC2201040D17D93DE81B58B86698B9A74E501BCC16C10182425B481511784BB5B4D7E5E7EC5CC04A853097FCCEE6154235A47247F469D3CC869AA485B627EC46324520A11C93C752E8EA5B8E5A0E3D25ADF553CA177C965CD5B16F742EC6C60A18619065047F2B2347C07F75943B233BE0CE0AEB3452AA314663D4FF3B5EF5AFF05457333F6
Mageshwaran R5y ago
try this query:
create table emps
(
      name varchar(50),
      dept varchar(10),
      empImg image
)
GO

--insert statement with single_blob to upload image to SQL Server
INSERT INTO emps ([Name],dept,empImg)
SELECT 'Akash','MIS',
(select * FROM OPENROWSET(BULK 'C:\01.JPG', SINGLE_BLOB) AS img)
GO


select * from emps
GO

update emps
set empImg=(select * FROM OPENROWSET(BULK 'C:\02.JPG', SINGLE_BLOB) AS img), dept='IT'
where name='Akash'
GO

--check the data whether it has been updated
select * from emps
go