varbinary转换成字符串.sql

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[varbin2hexstr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[varbin2hexstr]
GO

/*--varbinary转换成字符串

--邹建 2004-09(引用请保留此信息)--*/

/*--测试示例

   select dbo.varbin2hexstr(0x1432fad)
--*/
create function varbin2hexstr(
@bin varbinary(8000)
)returns varchar(8000)
as
begin
   declare @re varchar(8000),@i int
   select @re='',@i=datalength(@bin)
   while @i>0
       select @re=substring('0123456789ABCDEF',substring(@bin,@i,1)/16+1,1)
               +substring('0123456789ABCDEF',substring(@bin,@i,1)%16+1,1)
               +@re
           ,@i=@i-1
   return('0x'+@re)
end
go

chaunceyhao