把二进制的字段以字符串形式Select出来

首先我们以一个Table为例，某个字段为二进制形式(binary),在查询管理器中Select出来就是形如”0x0A1F8697FF0000000000”，问题是存储过程不能返回二进制字段，不知道有什么办法可以Select出来变为“0A1F8697FF00”这样一个字符串呢？下文中介绍的这个字段以FF00为结束，FF00后面的字符忽略。

-------------------------select cast(left(0x0A1F8697FF0000000000,charindex(0xFF00,0x0A1F8697FF0000000000)) as varbinary(8000)) as str--------------------------CREATE TABLE TEST6(A binary(50)) --DROP TABLE TEST6INSERT TEST6 SELECT 0x0A1F8697FF0000000000select cast(left(A,charindex(0xFF00,A)) as varbinary(8000)) as str from test6 -------------------------declare @bin varbinary(1000)declare @result varchar(1000)set @bin = 0x0A1F8697FF0000000000EXEC master.dbo.xp_varbintohexstr @bin, @result output----去掉字符串前面的'0x'字符set @result = stuff(@result,1,2,'')----去掉最后的FF00后面的字符(当有多个FF00时只从最后一个FF00截取)select reverse(substring(reverse(@result),charindex(reverse('FF00'),reverse(@result)),len(@result)- charindex(reverse('FF00'),reverse(@result))+1))/*结果0A1F8697FF00*/