Convert Image DataType To String in SQL Server

Question

I have a Column having Image datatype in MS-SQL SERVER 2012. I want to get it as string in SQL Query..

I have Tried This:

SELECT 
'empphoto : '+ ISNULL(CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), empphoto)),'') 
from emppersonal where --some condition

--empphoto Columns is of Image datatype

output looks corrupted (Just 4 characters).

OutPut looks like:

empphoto : ÿØÿà

enter image description here

How can be an Image datatype be converted to string in MS-SQL Server?

Before Updating Any row in table, I have to save that row data along with column name in some another table as a single string — Kartikeya Khosla
– Kartikeya Khosla, Commented Dec 1, 2014 at 8:45

Mikael Eriksson · Accepted Answer · 2014-12-01 09:25:19Z

11

You can extract the image value as BASE64 by running it through for xml path().

Try:

select 'empphoto : '+(select empphoto as '*' for xml path(''))

Result will look something like this.

empphoto : /9j/4AAQSkZJRgABAQAAAQABAAD/wAARCADw

To go the other way you have to remove the first 11 characters (empphoto :), cast to XML and extract the value as varbinary(max)..

select cast(stuff(YourTextColumn, 1, 11, '') as xml).value('.', 'varbinary(max)')

edited Dec 1, 2014 at 9:25

answered Dec 1, 2014 at 9:08

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kartikeya Khosla Over a year ago

ok,..If I have to insert it back to the same column empphoto Image datatype then how to cast it back

Kartikeya Khosla Over a year ago

It looks working for varbinry(max) conversion. and not for Image datatype conversion

Kartikeya Khosla Over a year ago

If I have to insert it back to the same column empphoto which is of Image datatype not varbinary(max).Then shouldn't I need to cast to Image datatype instead of varbinary(max) as done above

Mikael Eriksson Over a year ago

@user3879765 There is an implicit conversion from varbinary(max) to image. Have you tried it? You should not change the code that extract the data. Keep varbinary(max) in there and an insert to an image field should work just fine. If not you have to show the code you use with table structures and the error message you get.

Mikael Eriksson Over a year ago

@user3879765 To remove the implicit conversion you can do an explicit conversion instead. cast(cast(stuff(YourTextColumn, 1, 11, '') as xml).value('.', 'varbinary(max)') as image)

Collectives™ on Stack Overflow

Convert Image DataType To String in SQL Server

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related