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

dn001 2009-06-01 01:49:23

首先我们以一个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 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
*/