--创建16进制转换字符串函数
CREATE function fn_hex_to_char (
@x varbinary(100), -- binary hex value
@l int -- number of bytes
) returns varchar(200)
as
-- Written by: Gregory A. Larsen
-- Date: May 25, 2004
-- Description: This function will take any binary value and return
-- the hex value as a character representation.
-- In order to use this function you need to pass the
-- binary hex value and the number of bytes you want to
-- convert.
begindeclare @i varbinary(10)
declare @digits char(16)
set @digits = '0123456789ABCDEF'
declare @s varchar(100)
declare @h varchar(100)
declare @j int
set @j = 0
set @h = ''
-- process all bytes
while @j < @l
begin
set @j= @j + 1
-- get first character of byte
set @i = substring(cast(@x as varbinary(100)),@j,1)
-- get the first character
set @s = cast(substring(@digits,@i%16+1,1) as char(1))
-- shift over one character
set @i = @i/16
-- get the second character
set @s = cast(substring(@digits,@i%16+1,1) as char(1)) + @s
-- build string of hex characters
set @h = @h + @s
end
return(@h)
end
go--调用
select dbo.fn_hex_to_char(convert(varbinary,100),4)
CREATE function fn_hex_to_char (
@x varbinary(100), -- binary hex value
@l int -- number of bytes
) returns varchar(200)
as
-- Written by: Gregory A. Larsen
-- Date: May 25, 2004
-- Description: This function will take any binary value and return
-- the hex value as a character representation.
-- In order to use this function you need to pass the
-- binary hex value and the number of bytes you want to
-- convert.
begindeclare @i varbinary(10)
declare @digits char(16)
set @digits = '0123456789ABCDEF'
declare @s varchar(100)
declare @h varchar(100)
declare @j int
set @j = 0
set @h = ''
-- process all bytes
while @j < @l
begin
set @j= @j + 1
-- get first character of byte
set @i = substring(cast(@x as varbinary(100)),@j,1)
-- get the first character
set @s = cast(substring(@digits,@i%16+1,1) as char(1))
-- shift over one character
set @i = @i/16
-- get the second character
set @s = cast(substring(@digits,@i%16+1,1) as char(1)) + @s
-- build string of hex characters
set @h = @h + @s
end
return(@h)
end
go--调用
select dbo.fn_hex_to_char(convert(varbinary,100),4)
returns varchar(200)
as
begin
declare
@vtemp varchar(100),
@vout varchar(200),
@c varchar(100),
@vc char(10)
set @vtemp=14 --9899
if @vtemp<16
begin
if @vtemp<=9
set @vtemp=@vtemp
else
begin
set @vtemp=left(@vtemp,2)
-- print @vtemp
set @vtemp=char(cast(@vtemp as int)+55)
-- print @vtemp
end
return @vtemp
end
set @vout=''
while @vtemp>=16
begin
set @c= @vtemp %16
if @c >9
begin
set @c=char(cast(@c as int)+55)
end set @vout=@vout+@c
set @vtemp=@vtemp/16
end --while
set @vtemp= @vtemp+@vout
return @vtemp
end select dbo.f_16('14')E