首先来建立一个把数据转变成十六进制字符串输出的函数:
CREATE FUNCTION s_hexadecimal
(@binvalue varbinary(255))
RETURNS varchar(255)
AS
BEGIN
declare @charvalue varchar(255)
declare @i int
declare @length int
declare @hexstring char(16)
select @charvalue = '0x'
select @i = 1
select @length = datalength(@binvalue)
select @hexstring = '0123456789abcdef'
while (@i <= @length)
begin
declare @tempint int
declare @firstint int
declare @secondint int
select @tempint = convert(int, substring(@binvalue,@i,1))
select @firstint = floor(@tempint/16)
select @secondint = @tempint - (@firstint*16)
select @charvalue = @charvalue +
substring(@hexstring, @firstint+1, 1) +
substring(@hexstring, @secondint+1, 1)
select @i = @i + 1
end
return @charvalue
END
普通的函数方法是:
select '返回值' = dbo.s_hexadecimal(convert(varbinary(255), 256))
其实也可以这样来调用:
declare @bin varbinary(255)
select @bin = convert(varbinary(255), 1024)
execute s_hexadecimal @bin
select '返回值' = @bin
CREATE FUNCTION s_hexadecimal
(@binvalue varbinary(255))
RETURNS varchar(255)
AS
BEGIN
declare @charvalue varchar(255)
declare @i int
declare @length int
declare @hexstring char(16)
select @charvalue = '0x'
select @i = 1
select @length = datalength(@binvalue)
select @hexstring = '0123456789abcdef'
while (@i <= @length)
begin
declare @tempint int
declare @firstint int
declare @secondint int
select @tempint = convert(int, substring(@binvalue,@i,1))
select @firstint = floor(@tempint/16)
select @secondint = @tempint - (@firstint*16)
select @charvalue = @charvalue +
substring(@hexstring, @firstint+1, 1) +
substring(@hexstring, @secondint+1, 1)
select @i = @i + 1
end
return @charvalue
END
普通的函数方法是:
select '返回值' = dbo.s_hexadecimal(convert(varbinary(255), 256))
其实也可以这样来调用:
declare @bin varbinary(255)
select @bin = convert(varbinary(255), 1024)
execute s_hexadecimal @bin
select '返回值' = @bin