SQL SERVER Convert BLOB data to STRING

Question

I am trying to extract blob data from a SQL SERVER 2008 R2 database. The data is annotation data that an application stores. I have done some research and I have been partially successful in converting the data. I get about 20-30 first characters of each entry. This is the SQL statement I used:

select CONVERT(varchar(MAX), CONVERT(varbinary, blob)) from annotation_data;

How can I get all the data?

• Mikael Eriksson: What data type is blob? If it is a varbinary already then you really don't need to cast it to varbinary before you cast it to varchar..

Answer 1

The value truncates because you have not specified a length for the convert to varbinary. Without a specific length you get the default which is 30.

select convert(varchar(max), convert(varbinary, '1234567890123456789012345678901234567890'));
select convert(varchar(max), convert(varbinary(max), '1234567890123456789012345678901234567890'));

Result:

123456789012345678901234567890
1234567890123456789012345678901234567890