Convert Image DataType To String in SQL Server
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)')
Kartikeya Khosla
Currently working in Asp.Net MVC having more than 8 years of experience in Microsoft technologies. Technologies Known :- 1.) C# 2.) ASP.NET/ASP.NET Core 3.) WebForms/WinForms/DevExpress/Kendo 4.) ASP.NET MVC 3/4/5/6 5.) WEB API/SignalR 6.) LINQ/Entity Framework 7.) Sql Server 2008/2012/2014/2017 8.) Javascript/jQuery/Ajax 9.) HTML5/CSS3/Bootstrap 10.) AngularJS/Angular 2/5/8/9/10/11 11.) ReactJS, BackBoneJS, VueJS(Limited Proficiency)
Updated on December 01, 2020Comments
-
Kartikeya Khosla over 3 years
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?