CODEWITHSUNDEEP
sqlsql-serverblobdynamics-nav
user822448
user822448

Reputation: 683

BLOB to String, SQL Server

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

Upvotes: 16

Views: 168321

Answers (6)

chris
chris

Reputation: 1785

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

Upvotes: 16

imratE
imratE

Reputation: 1

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;

Upvotes: 0

sam yi
sam yi

Reputation: 4934

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

Upvotes: 0

user822448
user822448

Reputation: 683

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!

Upvotes: 8

visakh
visakh

Reputation: 2553

Can you try this:

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

Upvotes: 1

Yuck
Yuck

Reputation: 50855

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);

Upvotes: 4

Related Questions