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 : ÿØÿà

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

Answer 1

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)')