在SQL Server上将varbinary转换为字符串

I have been trying to convert a vabinary column to a string in SQL Server 2000. I have tried the recommended methods on this post: varbinary to string on SQL Server but not had any luck yet.

我一直在尝试将一个vabinary列转换为SQL Server 2000中的字符串。我在这篇文章中尝试了推荐的方法：varbinary to SQL Server上的字符串，但还没有任何运气。

I have it working in SQL Server 2008, just not in SQL Server 2000.

我让它在SQL Server 2008中工作，而不是在SQL Server 2000中。

My binary is

我的二进制是

0x000000000000000000000000000000000000000000000000000000000000C0FFFF000000000000

When I use select

当我使用选择

CONVERT(VARCHAR(1000), @data, 0) 

in SQL Server 2008 it works fine, but when I use it in SQL Server 2000 the output I get is

在SQL Server 2008中它工作正常，但当我在SQL Server 2000中使用它时，我得到的输出是

'ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ'

Has anyone come across this issue?

有人遇到过这个问题吗？

1 个解决方案

#1

you can do it SQL Server 2005/2000 by using XML.

你可以使用XML来做SQL Server 2005/2000。

DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x000000000000000000000000000000000000000000000000000000000000C0FFFF000000000000

SELECT @bin AS actualvalue,
        CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS StringValue