Converting Image to varchar

Question

Converting Image to varchar

jaura

Mr or Mrs. 500

Points: 587
More actions
April 4, 2010 at 11:31 pm

#386644

I want to Convert image field in my table to varchar but when I use:
select convert(varchar(max),convert(varbinary(max),ImgField)), Id from Table1
it returns ÿØÿà
I am hanging on this for last 4 days but nt finding anything about this...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

if you are sure the image field contains text that was converted to varbinary, then you need to convert to nvarchar isntead of varchar i think.

here's a proof of concept: the first part works perfectly, where the second, using varchar instead of nvarchar, does not convert correctly:

DECLARE @var VARBINARY(128),

@res NVARCHAR(64)

SET @var = CAST(N'Hello World' AS VARBINARY(128))

PRINT @var

--results: 0x480065006C006C006F00200057006F0072006C006400

SET @res = CAST(@var AS NVARCHAR(64))

PRINT @res

--results: Hello World

--The same but using CONVERT:

--####################

--fails because it's not nvarchar!

--####################

DECLARE @var VARBINARY(128),

@res VARCHAR(64)

SET @var = CAST('Hello World' AS VARBINARY(128))

PRINT @var

--results: 0x48656C6C6F20576F726C64

SET @res = CAST(@var AS NVARCHAR(64))

PRINT @res

--results: ?????d

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jaura Mr or Mrs. 500 Points: 587 More actions · Answer 2

No actually its an image data in it.

The purpose for convertion is To generate InsertScripts for the table. For this I want string representaion of the Image Data. When I use

select convert(varchar(max),convert(varbinary(max),imagefield)) from table1

it returns ÿØÿà

jaura Mr or Mrs. 500 Points: 587 More actions · Answer 3

Please reply. its very important. I am running out of time

Lowell SSC Guru Points: 323504 More actions · Answer 4

i've never scripted images out, mostly because they can't be represented in a string;

this link might help you, but everything i've ever seen shows that you have to treat images differently...

http://stackoverflow.com/questions/271979/copying-blob-values-between-databases-with-pure-sql-in-sql-server

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!