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

-------------------------

select cast(left(0x0A1F8697FF0000000000,charindex
(0xFF00,0x0A1F8697FF0000000000)) as varbinary(8000)) as str

--------------------------

CREATE TABLE TEST6(A binary(50)) --DROP TABLE TEST6
INSERT TEST6 SELECT 0x0A1F8697FF0000000000
select cast(left(A,charindex(0xFF00,A)) as varbinary(8000)) as str from test6

 -------------------------

declare @bin varbinary(1000)
declare @result varchar(1000)
set @bin = 0x0A1F8697FF0000000000
EXEC 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
*/