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

Question

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.

Trang Bartell · Answer

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 R · Answer

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