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
This question is related to
sql
sql-server
blob
dynamics-nav
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
Can you try this:
select convert(nvarchar(max),convert(varbinary(max),blob_column)) from table_name
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);
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;
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...
Source: Stackoverflow.com