在SQL Server 2008中将图像数据类型转换为varchar

Question

我们在 SQL Server 中有一个 TestPartner 数据库。缺陷的描述存储在 "image" 数据类型列中。我们需要编写一个查询来将数据显示为 HTML 表格。我们有这个查询，它从相应的表中读取数据，使用 For XML 将信息显示为 xml。但是将图像数据类型转换为 varchar 会引发异常：“FOR XML 无法序列化节点 'TD' 中包含不允许在 XML 中的字符（0x0002）的数据。要使用 FOR XML 检索此数据，请将其转换为二进制、varbinary 或图像数据类型，并使用 BINARY BASE64 指令。”

查询：

DECLARE @ResultsTable nvarchar(MAX)
--Create the XML table with the query results
SET @ResultsTable =
N'<H3>QA Automation Tests Results Summary </H3>' +
N'<table border="1">' +
N'<tr><th>Test Name</th><th>Execution Date</th>' +
N'<th>Check Name</th><th>Description</th></tr>' +
CAST ( (
select distinct Name as TD, '',
(Select CAST(CONVERT(nchar(100),CAST( TPCommandDetail AS BINARY(100) )) as VARCHAR(100)) ) as TD, ''
FROM TestPartnerDB.TP_RESULTS_RECORDS
FOR XML PATH('tr'), TYPE 
) AS nvarchar(max) ) + N'</table>'

SELECT @ResultsTable

令人惊讶的是，它对一些记录有效，但是当您将大小增加到200时，它又会再次出错。我还尝试过：

Select CONVERT(varchar(1000), convert(varbinary(1000), tpcommanddetail)) From TestPartnerDB.TP_RESULTS_RECORDS

这会返回每一行奇怪的字符。有人知道如何让这个东西正常工作吗？

- Sri Reddy

---

将文本数据存储在二进制字段中，这必须是一个非常令人费解的举动——祝你好运。 - DJ.

Answer 1

简单的答案是

select cast(cast(my_column as varbinary(max)) as varchar(max)) as column_name
from   my_table

这将把列转换为varchar格式。如果您有Unicode数据，nvarchar(max)可能更好。

- kristianp

Answer 2

你可以这样进行转换

convert (varchar(max) , convert (varbinary (max) , blob_data)), cast(cast(blob_data as binary) as varchar(max)) 

- NIrav Modi

---

这个方案虽然可行，但它截断了结果字符串。最终我使用了SELECT CONVERT(xml, CONVERT(varbinary(MAX), Fields))。 - Slogmeister Extraordinaire

Answer 3

尝试使用这段代码：

Select  MASTER.dbo.Fn_varbintohexstr(tpcommanddetail) From TestPartnerDB.TP_RESULTS_RECORDS

- Ahmad Aghazadeh

Answer 4

如果数据以Unicode数据的形式存储在图像字段中，那么如果您用Select CONVERT(varchar(1000), convert(varbinary(1000), tpcommanddetail)) From TestPartnerDB.TP_RESULTS_RECORDS替换该行，则它将无法正常工作。非常重要的是，从二进制数据到文本的第一次转换必须使用正确的排序规则和字符大小：如果文本为Ascii，则必须使用varchar（），如果文本为Unicode，则必须使用nvarchar（）。 对于我来说，从nvarchar（100）到varchar（100）的第二次转换看起来毫无用处。对于我来说，使用binary（100）而不是varbinary（100）也很可疑。最后，如果您遇到奇怪的字符，例如0x0002，则可能是因为这些内容被存储在图像字段而不是文本字段中：这是一个特殊格式的字段，其中并非所有字符都是文本字符。但是，由于您没有向我们展示原始字段以二进制（或更确切地说是十六进制）或任何结果的打印结果，因此无法再发表更多评论。 我只是进行了一些测试; 通过这些测试，您应该能够理解发生了什么。

select ascii ('A'), unicode(N'A');
select convert (binary(2), ascii('A')), convert (binary(2), unicode(N'A'));
--
declare @ab binary(10), @vab varbinary(10);
declare @nab binary(10), @vnab varbinary(10);
--
set @ab = convert (binary (10), 'AB');
set @vab = convert (varbinary (10), 'AB');
set @nab = convert (binary (10), N'AB');
set @vnab = convert (varbinary (10), N'AB');
--
select @ab, @vab, @nab, @vnab;
--
select convert(varchar(10), @ab) + '*', 
    convert(varchar(10), @vab) + '*', 
    convert(varchar(10), @nab) + '*', 
    convert(varchar(10), @vnab) + '*';
--
select len(convert(varchar(10), @ab)), 
    len(convert(varchar(10), @vab)), 
    len(convert(varchar(10), @nab)), 
    len(convert(varchar(10), @vnab));
--
select len(convert(varchar(10), @ab) + '*'), 
    len(convert(varchar(10), @vab) + '*'), 
    len(convert(varchar(10), @nab) + '*'), 
    len(convert(varchar(10), @vnab) + '*');
--
select convert(nvarchar(10), @ab) + '*', 
    convert(nvarchar(10), @vab) + '*', 
    convert(nvarchar(10), @nab) + '*', 
    convert(nvarchar(10), @vnab) + '*';
--
select len(convert(nvarchar(10), @ab)), 
    len(convert(nvarchar(10), @vab)), 
    len(convert(nvarchar(10), @nab)), 
    len(convert(nvarchar(10), @vnab));
--
select convert(varchar(10), convert(nvarchar(10), @ab)) + '*', 
    convert(varchar(10), convert(nvarchar(10), @vab)) + '*', 
    convert(varchar(10), convert(nvarchar(10), @nab)) + '*', 
    convert(varchar(10), convert(nvarchar(10), @vnab)) + '*';
--
select len(convert(varchar(10), convert(nvarchar(10), @ab))), 
    len(convert(varchar(10), convert(nvarchar(10), @vab))), 
    len(convert(varchar(10), convert(nvarchar(10), @nab))), 
    len(convert(varchar(10), convert(nvarchar(10), @vnab)));
--
select convert(nvarchar(10), @nab) for xml path('tr');
select convert(varchar(10), convert(nvarchar(10), @nab)) for xml path('tr');
select 'The Name' as td, '', convert(nvarchar(10), @nab) as td for xml path('tr');

- SylvainL

Answer 5

我的猜测是，您存储在图像列中的数据不是“普通”文本 - 我猜它是一些任意的数据结构（因此决定使用图像而不是varchar）？我尝试过这个没有问题：

    declare @data varchar(max)

    declare @fred table (d1 varchar(max), d2 xml, d3 image)

    set @data = 'here is some data'
    while (len(@data) < 200)    set @data = @data + ' ' + cast(rand() as varchar)

    insert into @fred (d1,d2,d3) values (@data,@data,@data)

    set @data = 'here is some more data'
    while (len(@data) < 200)    set @data = @data + ' ' + cast(rand() as varchar)

    insert into @fred (d1,d2,d3) values (@data,@data,@data)

    declare @xml xml
    set @xml = (select cast(cast(d3 as varbinary(max)) as varchar(max)) as 'td' from @fred FOR XML PATH('tr'), TYPE)

    select @xml

- Black Light

Answer 6

对我有效的将IMAGE列转换的方法是：

SELECT convert(varchar(max), cast(mypicture as varbinary(max)), 1)
FROM MyTable

- Cameron Castillo