user822448 user822448 - 1 year ago 229
SQL Question

BLOB to String, SQL Server

I have a text string stored as a

data type in a database. I want to extract it by an SQL select query, but I have problems converting/casting from
to readable

I've tried e.g.

select convert(nvarchar(40),convert(varbinary(40),BLOBTextToExtract))
from [NavisionSQL$Customer]

I guess I need something similar, but I can't figure out exactly what I need to do the conversion. Can somebody please give me some directions?


Answer Source

Problem was apparently not the SQL server, but the NAV system that updates the field. There is a compression property that can be used on BLOB fields in NAV, that is not a part of SQL Server. So the custom compression made the data unreadable, though the conversion worked.

The solution was to turn off compression through the Object Designer, Table Designer, Properties for the field (Shift+F4 on the field row).

After that the extraction of data can be made with e.g.: select convert(varchar(max), cast(BLOBFIELD as binary)) from Table

Thanks for all answers that were correct in many ways!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download