BLOB转换为字符串，SQL Server [英] BLOB to String, SQL Server

问题描述

我有一个文本字符串，以BLOB数据类型存储在数据库中.我想通过SQL选择查询将其提取，但是在将BLOB转换/广播为可读的text时遇到问题.

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.

例如，我尝试过

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?

致谢

推荐答案

问题显然不是SQL Server，而是更新字段的NAV系统.在NAV的BLOB字段上有一个压缩属性，该属性不是SQL Server的一部分.因此，尽管转换有效，但自定义压缩使数据无法读取.

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.

解决方案是通过字段的对象设计器"，表设计器"，属性"(字段行上的Shift + F4)关闭压缩.

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

之后，可以使用以下方法提取数据: 选择convert(varchar(max)，cast(BLOBFIELD作为二进制文件)) 从表

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!

这篇关于BLOB转换为字符串，SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！