/*获取一个字节每一位的值1,0 0~7代表1到8位*/
CREATE FUNCTION dbo.GetBitX (@b binary(1),@w int )
RETURNS bit
AS
BEGIN
DECLARE
@m binary(1)
Select @m = (@b*POWER(2,7-@w))
Select @m=@m/POWER(2,7)
RETURN( @m)
END
GO
CREATE FUNCTION dbo.GetFLOATTX (@data binary(4))
RETURNS float
AS
BEGIN
DECLARE @flag int,@DATAZSW binary(1) ,@tmp int,@data1 binary(1),@data2 binary(1),@data3 binary(1),@data4 binary(1),@datafloat float,@i int,@num float
/*DECLARE @data binary(4)
set @data =0x420BEB85 0x420BEB85 = 0 1000010 0 0001011 11101011 10000101 34.98*/
/* set @data =0x420BEB85 0x43CE61BA = 01000011 11001110 01100001 10111010 412.7635*/
set @data1 = SUBSTRING(@data,1,1)
set @data2 = SUBSTRING(@data,2,1)
set @data3 = SUBSTRING(@data,3,1)
set @data4 = SUBSTRING(@data,4,1)
set @flag = dbo.GetBitX( @data1,7) /*取符号*/
set @DATAZSW = @data1*POWER(2,1)
set @tmp=@DATAZSW+@data2/POWER(2,7)-127/*取整数位数*/
set @datafloat = 1
set @num = 0
IF @tmp >0
BEGIN
Set @i =0
while @i<@tmp
BEGIN
if(@i<7)
set @datafloat= @datafloat*2+dbo.GetBitX( @data2,6-@i)
if(@i>=7 and @i<15)
set @datafloat= @datafloat*2+dbo.GetBitX( @data3,7-(@i-7))
if(@i>=15 and @i<23)
set @datafloat= @datafloat*2+dbo.GetBitX( @data4,7-(@i-15))
set @i=@i+1
END
set @tmp =23-@tmp
if(@tmp>0)
BEGIN
set @i = 0
while @i<@tmp
BEGIN
if(@i>=0 and @i<8)
set @num= @num/2+dbo.GetBitX( @data4,@i)/2.0
if(@i>=8 and @i<16)
set @num= @num/2+dbo.GetBitX( @data3,@i-8)/2.0
if(@i>=16 and @i<24)
set @num= @num/2+dbo.GetBitX( @data2,@i-16)/2.0
set @i=@i+1
END
set @datafloat= @datafloat+@num
END
END
IF @tmp =0
Begin
set @tmp =23
set @i = 0
while @i<@tmp
BEGIN
if(@i>=0 and @i<8)
set @num= @num/2+dbo.GetBitX( @data4,@i)/2.0
if(@i>=8 and @i<16)
set @num= @num/2+dbo.GetBitX( @data3,@i-8)/2.0
if(@i>=16 and @i<24)
set @num= @num/2+dbo.GetBitX( @data2,@i-16)/2.0
set @i=@i+1
END
set @datafloat= @datafloat+@num
end
IF @tmp <0
BEGIN
set @i = 0
while @i<23
BEGIN
if(@i>=0 and @i<8)
set @num= @num/2+dbo.GetBitX( @data4,@i)/2.0
if(@i>=8 and @i<16)
set @num= @num/2+dbo.GetBitX( @data3,@i-8)/2.0
if(@i>=16 and @i<24)
set @num= @num/2+dbo.GetBitX( @data2,@i-16)/2.0
set @i=@i+1
END
if(@tmp=-127 and @num=0)
BEGIN
set @datafloat =0
END
ELSE
BEGIN
set @datafloat= @datafloat+@num
set @datafloat= @datafloat/(POWER(2,@tmp*-1)*1.0)
END
END
if(@flag=1)
set @datafloat =@datafloat*-1.0
return(@datafloat)
END
GO
DECLARE @datashow binary(256)
set @datashow =0x45B614F445B5E22545B2A762461DB000461D8400461AB800415599ED415A4469416395E547E70A0847D7F3D8480451B4477BE97B479FB74F47163A3A4803924048064C8048098BC0410CDCD142F0000042F0000042F0000042F0000042F000003F60C49B3F4DD2F13F7645A14114ACD1424753F83F51EB853F8000003F8000003F8000003F8000003F8000003F800000000000000000000000000000000000003F8000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
Print dbo.GetFLOATTX(0xC2C7A840)
Print dbo.GetFLOATTX(substring(@datashow,5,4))
Print dbo.GetFLOATTX(substring(@datashow,1,4))
Print dbo.GetFLOATTX(substring(@datashow,9,4))
Print dbo.GetFLOATTX(substring(@datashow,13,4))