BLOB 到字符串，SQL Server

BLOB to String, SQL Server

提问by user822448

I have a text string stored as a BLOBdata type in a database. I want to extract it by an SQL select query, but I have problems converting/casting from BLOBto readable text.

我有一个文本字符串作为BLOB数据类型存储在数据库中。我想通过 SQL 选择查询提取它，但我在从 转换/转换BLOB为 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

问候

采纳答案by user822448

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.

问题显然不是 SQL 服务器，而是更新字段的 NAV 系统。有一个压缩属性可用于 NAV 中的 BLOB 字段，它不是 SQL Server 的一部分。因此，尽管转换有效，但自定义压缩使数据无法读取。

The solution was to turn off compression through the Object Designer, Table Designer, Properties for the field (Shift+F4 on the field row).

解决方案是通过对象设计器、表设计器、字段属性（字段行上的 Shift+F4）关闭压缩。

After that the extraction of data can be made with e.g.: select convert(varchar(max), cast(BLOBFIELD as binary)) from Table

之后可以使用例如：select convert(varchar(max), cast(BLOBFIELD as binary)) from Table 提取数据

Thanks for all answers that were correct in many ways!

感谢所有在很多方面都是正确的答案！

回答by Yuck

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

或者，如果这只是varchar...

SELECT CONVERT(VarChar(40), BLOBTextToExtract)
FROM [NavisionSQL$Customer];

I used this script to verify and test on SQL Server 2K8 R2:

我使用此脚本在 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);

回答by chris

The accepted answer works for me only for the first 30 characters. This works for me:

接受的答案仅适用于前 30 个字符。这对我有用：

select convert(varchar(max), convert(varbinary(max),myBlobColumn)) FROM table_name

回答by visakh

Can you try this:

你能试试这个吗：

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

回答by sam yi

Found this...

发现这个...

bcp "SELECT top 1 BlobText FROM TableName" queryout "C:\DesinationFolder\FileName.txt" -T -c'

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

如果您需要了解 bcp 标志的不同选项...

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

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

回答by imratE

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;