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

User · Accepted Answer

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!

User · Answer

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

User · Answer

Can you try this   select convert nvarchar max  convert varbinary max  blob column   from table name

User · Answer

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

User · Answer

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

User · Answer

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

[sql] BLOB to String, SQL Server

Examples related to sql

Examples related to sql-server

Examples related to blob

Examples related to dynamics-nav