15

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

Unreason
  • 12,556
  • 2
  • 34
  • 50
user822448
  • 673
  • 2
  • 8
  • 18

6 Answers6

14

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
chris
  • 1,685
  • 3
  • 18
  • 28
  • This should be the accepted answer if it includes also the hint to turn off compression on the BLOB Field. – pungggi Nov 26 '19 at 14:56
8

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!

user822448
  • 673
  • 2
  • 8
  • 18
  • 1
    I tried your solution, but I am getting the following error: `Explicit conversion from data type text to varbinary is not allowed.` Any ideas..?? – mithilatw May 29 '14 at 15:03
4

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);
Yuck
  • 49,664
  • 13
  • 105
  • 135
  • With the first, I get just chinese signs. The second returns D|Z for everything. – user822448 Aug 02 '11 at 11:57
  • I'm quite sure the text is forced stored as blob (image) – user822448 Aug 02 '11 at 11:58
  • What version of SQL Server are you using? – Yuck Aug 02 '11 at 11:58
  • What is the compatibility level of your database then? – Yuck Aug 02 '11 at 12:08
  • 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. – Yuck Aug 02 '11 at 12:24
  • @user822448 let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/2056/discussion-between-yuck-and-user822448) – Yuck Aug 02 '11 at 12:29
  • 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... – MichaelF Aug 16 '16 at 20:19
  • 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. – Palec Jun 29 '18 at 21:41
1

Can you try this:

select convert(nvarchar(max),convert(varbinary(max),blob_column)) from table_name
visakh
  • 2,503
  • 8
  • 29
  • 55
  • 4
    This returns just chinese signs. The datatype from the beginning is image (which I guess is a BLOB) – user822448 Aug 02 '11 at 11:58
  • To avoid chinese signs, it's better to use `select convert(varchar(max),convert(varbinary(max),blob_column)) from table_name` – BrainabilGH Apr 30 '19 at 10:15
0

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

sam yi
  • 4,806
  • 1
  • 29
  • 40
0
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;
Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
imratE
  • 1