SQL SERVER10进制与16进制互换

 SELECT int2Hex(89)
create function int2Hex
(
@DECINT SMALLINT
)
returns VARCHAR(20)
AS
BEGIN
declare @a int
declare @b int
declare @c varchar(20)

declare @t table(a int,b char(1))
insert into @t(a,b)
select 1,'1' union all
select 2,'2' union all
select 3,'3' union all
select 4,'4' union all
select 5,'5' union all
select 6,'6' union all
select 7,'7' union all
select 8,'8' union all
select 9,'9' union all
select 10,'a' union all
select 11,'b' union all
select 12,'c' union all
select 13,'d' union all
select 14,'e' union all
select 15,'f'

set @a = 138
set @c = ''

while @a > 0
begin
if @a >= 16
begin
set @b = @a/ 16
set @c = (select  @c + b + '0' from @t where a = @b)
set @a = @a % 16
end
else
begin
set @c=  (select case len(@c) when 0 then @c
      else left(@c,len(@c)-1)
end + b from @t where a = @a)
set @a = 0
end
end

 RETURN @C

END

 

CREATE FUNCTION dbo.Hex2Dec (@vNCarryValue varchar(100))
RETURNS dec
AS
BEGIN
declare @iValue dec(12,2)
,@iCount int
,@iLen int
,@iPos int,
@cChar int
select @iValue=0.00,@iCount=len(replace(@vNCarryValue,'0',''))
select @vNCarryValue = ltrim(rtrim(isnull(@vNCarryValue,'')))
while(len(@vNCarryValue)>=1)
begin
set @cChar = ascii(right(@vNCarryValue,1))
if (@cChar >= ascii('0') and @cChar <= ascii('9')) or (@cChar >= ascii('a') and @cChar <= ascii('f')) or (@cChar >= ascii('A') and @cChar <= ascii('F'))
begin
select @iValue = @iValue * 16 + (case when (@cChar >= ascii('0') and @cChar <= ascii('9')) then @cChar - ascii('0')
when (@cChar >= ascii('a') and @cChar <= ascii('f')) then @cChar - ascii('a')
when (@cChar >= ascii('A') and @cChar <= ascii('F')) then @cChar - ascii('A')
end)

select @vNCarryValue=substring(@vNCarryValue,1,len(@vNCarryValue)-1)
-- ,@iLen=len(@vNCarryValue)
-- ,@iPos=@iLen-charindex('1',@vNCarryValue,0)
-- ,@vNCarryValue=right(@vNCarryValue,len(@vNCarryValue)-
-- charindex('1',@vNCarryValue))
-- ,@iValue=@iValue+power(16,@iPos)
-- ,@iCount = @iCount-1
end
else
return 0;
end
return @iValue
END