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
set @t=0x468DB371
select convert(bigint,@t)
比如说 @vNCarryValue = '345614' 那么charindex('1',@vNCarryValue)返回 5感觉你这里用的不对,每次@pos = 0,这样每次加1,@iCount = 8,所以为 8
这下对了alter FUNCTION dbo.Hex2Dec (@vNCarryValue varchar(100))
RETURNS dec
AS
BEGIN
declare @iValue numeric(12,2)
,@iCount int
,@iLen int
,@iPos int,
@cChar int
select @iValue=0.00
select @vNCarryValue = ltrim(rtrim(isnull(@vNCarryValue,'')))
while(len(@vNCarryValue)>=1)
begin
set @cChar = ascii(left(@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') + 10
when (@cChar >= ascii('A') and @cChar <= ascii('F')) then @cChar - ascii('A') + 10
end)
select @vNCarryValue=substring(@vNCarryValue,2,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
468DB371 468CC133 44DBC8F9 44EB83E4 4217FBEB 41BC98B8 42966B6A下面是它对应的10进制 468DB371 = 18137.72
468CC133 = 18016.6
44DBC8F9= 1758.28
44EB83E4= 1884.122
4217FBEB= 37.99601
41BC98B8 = 23.57457
42966B6A= 75.20979
1、请问select len('12340sdf'):你觉得这个结果是4还是8
我测试过:结果是8,说明0的问题这里不用考虑
2、我认为无论用T-SQL语言写,还是用C写,解决问题的思路一定是清楚的
并且是合理的
3、我并没有模仿楼上所写,是本人自己写的
以下是我的解决方案:
也许不是最好的,但是至少是正确的,同时也希望各位高手提出更好的
的解决方案,大家共同学习,我认为T-SQL语言是非常强大的,应该认真学习
CREATE FUNCTION dbo.Hex2Dec(@vNCarryValue varchar(100))
RETURNS dec
AS
BEGIN
declare @iValue dec(12,2),
@iCount int,
@iLen int,
@iPos int,
@CurrentLen int
--定义局部变量
select @iValue=0.00,@iCount=len(@vNCarryValue)
set @CurrentLen = @iCount
--ivalue是用来存储最终结果
--@iCount是用来存储有多少个16进制字符,以决定他的最高幂次(@iCount-1)
declare @temp int
while(@CurrentLen>=1)
begin
--程序做的第一件事情:取最左边或最右边的一个字符,
--这里按照作者意思,取最右边的一个字符,取出它的ASCII值
set @temp = ASCII(right(@vNCarryValue,1))
if(@temp>=65 and @temp <=70)
set @temp = @temp-55
if(@temp>=97 and @temp <=102)
set @temp = @temp-87
if(@temp>=48 and @temp <=57)
set @temp = @temp-48
--这里最好加一些判断(省略)
--程序做的第二件事情:累加操作
set @iValue =@iValue+@temp*power(16,@iCount - @CurrentLen)
--程序做的第三件事情:截取字符串
set @CurrentLen = @CurrentLen - 1
set @vNCarryValue = substring(@vNCarryValue,1,@CurrentLen)
end return @iValue
END测试程序:
select dbo.Hex2Dec('ab')说明:其实程序需要作出很多判断会不会益出现象,呵呵
有什么问题[email protected] 联系吧 桂林电子科技大学计算机系
信息管理与信息系统
2006.4.27
CREATE function Hex2Bin(
@h varchar(50)
)
returns varchar(100)
as
begin
declare @r varchar(100)
set @r=''
declare @i int
set @i=1
declare @t varchar(2)
while @i<=len(@h)
begin
set @t=substring(@h,@i,1)
select @r=@r+case @t
when '0' then '0000'
when '1' then '0001'
when '2' then '0010'
when '3' then '0011'
when '4' then '0100'
when '5' then '0101'
when '6' then '0110'
when '7' then '0111'
when '8' then '1000'
when '9' then '1001'
when 'A' then '1010'
when 'B' then '1011'
when 'C' then '1100'
when 'D' then '1101'
when 'E' then '1110'
when 'F' then '1111'
end
set @i=@i+1
end
return @r
end
go
--2进制转整数,计算整数部分
create function bin2int(
@b varchar(100)
)
returns int
as
begin
declare @r int
set @r=0
declare @i int
set @i=1
while @i<=len(@b)
begin
set @r=@r*2+substring(@b,@i,1)
set @i=@i+1
end
return @r
endgo--2进制转小数部分,本来应该是用float的,为防止溢出,私自扩大了
CREATE function bin2rea(
@b varchar(100)
)
returns real
as
begin
declare @r real
declare @x real
set @r=0
declare @i int
set @i=1
set @x=0.5
while @i<=len(@b)
begin
set @r=@r+@x*cast(substring(@b,@i,1) as int)
set @i=@i+1
set @x=@x/2
end
return @r
endgo
--32位16进制浮点数转换成10进制函数
--作者:Yang_
--欢迎转贴
create function Hex2Dec(
@h varchar(50)
)
returns real
as
begin
declare @r real
set @r=0
declare @b varchar(100)
declare @w varchar(100) --尾数
declare @z varchar(100) --指数
declare @f int --符号
declare @zs real
declare @xs real
set @b=dbo.Hex2Bin(@h)
set @w=substring(@b,10,23)
set @z=substring(@b,2,8)
if left(@b,1)='1'
set @f=-1
else
set @f=1
declare @zws int
set @zws=dbo.bin2int(@z)-127
set @zs=dbo.bin2int('1'+substring(@w,1,@zws))
set @xs=dbo.bin2rea(substring(@w,@zws+1,23-@zws))
set @r=@f*(@zs+@xs)
return @r
endgo--调用
select dbo.Hex2Dec('468DB371')
@h varchar(50)
)
returns real
as
begin
--32位16进制浮点数转换成10进制函数
--作者:Yang_
--欢迎转贴
declare @r real
set @r=0
declare @b varchar(100)
declare @w varchar(100) --尾数
declare @z varchar(100) --指数
declare @f int --符号
declare @zs real
declare @xs real
set @b=dbo.Hex2Bin(@h)
set @w=substring(@b,10,23)
set @z=substring(@b,2,8)
if left(@b,1)='1'
set @f=-1
else
set @f=1
declare @zws int
set @zws=dbo.bin2int(@z)-127
if @zws>0
begin
set @zs=dbo.bin2int('1'+substring(@w,1,@zws))
set @xs=dbo.bin2rea(substring(@w,@zws+1,23-@zws))
end
else
begin
set @zs=0
set @xs=dbo.bin2rea(REPLICATE ('0' ,-@zws-1 )+'1'+@w)
end
set @r=@f*(@zs+@xs)
return @r
end
GO