[sql] 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

This question is related to sql sql-server blob dynamics-nav

The answer is


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...

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


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to blob

Angular: How to download a file from HttpClient? How do we download a blob url video How to convert Blob to File in JavaScript Saving binary data as file using JavaScript from a browser Inserting Image Into BLOB Oracle 10g How to Display blob (.pdf) in an AngularJS app Difference between CLOB and BLOB from DB2 and Oracle Perspective? JavaScript blob filename without link How to convert Blob to String and String to Blob in java How to go from Blob to ArrayBuffer

Examples related to dynamics-nav

BLOB to String, SQL Server