How can I view the contents of sql binary data fields?

Question

Does anybody know how to view the contents of fields containing binary data in an MS SQL Server 2005 database?

It may be a string in which case viewing it in ascii or hex form would suffice. At the moment all I see is <Binary data>. — Xandir
– Xandir, Commented Feb 10, 2009 at 15:36

SQLMenace · Accepted Answer · 2009-02-10 15:41:01Z

23

Depends if it is just text stored as binary or not, if it is then take a look at this

create table #bla (col1 varbinary(400))

insert #bla values(convert(varbinary(400),'abcdefg'))

select col1,convert(varchar(max),col1)
from #bla

output 0x61626364656667 abcdefg

answered Feb 10, 2009 at 15:41

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

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

1 Comment

Thanks. I was hoping for a utility to take some of the legwork out of it but this is a workable solution.