4

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?

Kartikeya Khosla
  • 18,743
  • 8
  • 43
  • 69

1 Answers1

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)')
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • ok,..If I have to insert it back to the same column empphoto `Image` datatype then how to cast it back – Kartikeya Khosla Dec 01 '14 at 09:20
  • It looks working for `varbinry(max)` conversion. and not for `Image` datatype conversion – Kartikeya Khosla Dec 01 '14 at 09:44
  • 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 – Kartikeya Khosla Dec 01 '14 at 09:48
  • @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 Dec 01 '14 at 09:50
  • @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)` – Mikael Eriksson Dec 01 '14 at 09:52