BLOB to String, SQL Server

Question

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

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?

Regards

Fancywork

Answer 1

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!

Fancywork

I tried your solution, but I am getting the following error: Explicit conversion from data type text to varbinary is not allowed. Any ideas..?? – Retsina

Answer 2

The accepted answer works for me only for the first 30 characters. This works for me:

select convert(varchar(max), convert(varbinary(max),myBlobColumn)) FROM table_name

Lebar

This should be the accepted answer if it includes also the hint to turn off compression on the BLOB Field. – Kalamazoo

Answer 3

It depends on how the data was initially put into the column. Try either of these as one should work:

SELECT CONVERT(NVarChar(40), BLOBTextToExtract)
FROM [NavisionSQL$Customer];

Or if it was just varchar...

SELECT CONVERT(VarChar(40), BLOBTextToExtract)
FROM [NavisionSQL$Customer];

I used this script to verify and test on SQL Server 2K8 R2:

DECLARE @blob VarBinary(MAX) = CONVERT(VarBinary(MAX), 'test');

-- show the binary representation
SELECT @blob;

-- this doesn't work
SELECT CONVERT(NVarChar(100), @blob);

-- but this does
SELECT CONVERT(VarChar(100), @blob);

Delft

With the first, I get just chinese signs. The second returns D|Z for everything. – Fancywork

I'm quite sure the text is forced stored as blob (image) – Fancywork

What version of SQL Server are you using? – Delft

What is the compatibility level of your database then? – Delft

Right-click your database in SSMS and select Properties. In the Options page there is a setting called compatibility. What value does it have? For instance 80 = SQL 2000. – Delft

@Fancywork let us continue this discussion in chat – Delft

Second one did it for my SQL 2012 server, though I needed to change the VarChar to 500 since I have a lot of data stored in my blob... – Ikkela

With my VARBINARY(MAX) column, CONVERT(NVARCHAR(MAX), column) gives Chinese characters while CONVERT(VARCHAR(MAX), column) gives the correct UTF-8 data stored inside. – Emrick

Answer 4

Can you try this:

select convert(nvarchar(max),convert(varbinary(max),blob_column)) from table_name

Tacit

This returns just chinese signs. The datatype from the beginning is image (which I guess is a BLOB) – Fancywork

To avoid chinese signs, it's better to use select convert(varchar(max),convert(varbinary(max),blob_column)) from table_name – Commensal

@Commensal It worked great in SQL 2016 and NAV 2018, thanks! – Blowhole

Answer 5

Found this...

bcp "SELECT top 1 BlobText FROM TableName" queryout "C:\DesinationFolder\FileName.txt" -T -c'

If you need to know about different options of bcp flags...

http://msdn.microsoft.com/en-us/library/ms162802.aspx

Limon

Answer 6

CREATE OR REPLACE FUNCTION HASTANE.getXXXXX(p_rowid in rowid) return VARCHAR2
  as
          l_data long;
  begin
         select XXXXXX into l_data from XXXXX where rowid = p_rowid;
         return substr( l_data, 1, 4000);
  end getlabrapor1;

Dropforge