30.varbinary转换成字符串

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转换成字符串
	
*/

/*--测试示例

	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