此 Stored Procedure 可將輸入的十進制數字轉換為 IEEE 754 標準二進制浮點格式 (32 bits) 輸出。
IEEE 754 共分為 S (Sign)、E (Exponent)、M (Mantisa) 三個部分 -
計算方式： 以 -60.25 為例
1) -60.25 為負數，將 S 設定為 1 (若為正數則設定為 0)。
2) 將 60.25 轉換為二進制浮點數 111100.01
3) 將此二進制浮點數正規劃 1.1110001 * 2^5
4) 將 5 (2 的乘方) 加上 127 後，轉換為二進制 10000100，存放於 E。
5) 將 1.1110001 去掉最前面的 "1." 後，存放於 M (共 32 bits，不足補 0)；得到 11100010000000000000000 。
6) 最後得到 1 10000100 11100010000000000000000
程式碼
if object_id ('usp_IEEE754_Generate') is not null
drop proc usp_IEEE754_Generate
go
create proc usp_IEEE754_Generate @prm_num float,
@rtn_IEEE_754_string varchar(32) output,
@rtn_IEEE_754_binary_codes binary(32) output
/*----------------------------------------------------------------------------------+
| Author: Wei-jie Yang |
| Date: 2012/05/10 |
| All Rights Reserved |
| |
| [Description] |
| This stored procedure is used to convert decimal floating number into binary |
| floating number according to IEEE 754 (Standard for Binary Floating-Point |
| Arithmetic). |
| |
| [Parameters] |
| (1).INPUT Parameter |
| - @prm_num: The number is going to be converted. |
| (2).OUTPUT Parameter |
| - @rtn_IEEE_754_string: Converted number stored in varchar data type |
| - @rtn_IEEE_754_codes : Converted number stored in binary data type |
| |
| [Usage] |
| declare @return_1 varchar(32), @return_2 binary(32) |
| exec usp_IEEE754_Generate -0.75, @return_1 output, @return_2 output |
| select @return_1 as Varchar_Mode |
| select @return_2 as Binary_Mode |
+----------------------------------------------------------------------------------*/
as
declare @dot_position int,
@exp_part_0_1 varchar(8),
@fraction_part float,
@fraction_part_0_1 varchar(23),
@i int,
@integer_fraction_0_1 varchar(100),
@integer_part int,
@integer_part_0_1 varchar(23),
@j int,
@k int,
@leading_one_position int,
@sign char(1), -- 0: Zero, Positive Number 1: Negative Number
@single_digit char(1),
@temp_exp int,
@temp_fraction float,
@temp_integer int
declare @integer_result table (
i_sn int,
i_digit char(1)
)
declare @exp_result table (
e_sn int,
e_digit char(1)
)
set nocount on
-- Initialization ---------------------------------------------------
if @prm_num = 0
goto Zero_Handler
if @prm_num > 0
begin
select @sign = '0'
end
else
begin
select @sign = '1'
select @prm_num = @prm_num * -1
end
select @integer_part = @prm_num / 1
select @fraction_part = @prm_num - @integer_part
---------------------------------------------------------------------
-- Integer Part -----------------------------------------------------
select @temp_integer = @integer_part
select @i = 1
while @temp_integer > 1
begin
select @single_digit = @temp_integer % 2
select @temp_integer = @temp_integer / 2
insert into @integer_result values(@i, @single_digit)
select @i = @i + 1
end
insert into @integer_result values(@i, @temp_integer)
select @integer_part_0_1 = ''
while @i > 0
begin
select @single_digit = i_digit
from @integer_result
where i_sn = @i
select @integer_part_0_1 = @integer_part_0_1 + @single_digit
select @i = @i - 1
end
---------------------------------------------------------------------
-- Fraction Part ----------------------------------------------------
select @temp_fraction = @fraction_part
select @j = 1
select @fraction_part_0_1 = ''
if @temp_fraction > 0
begin
while @temp_fraction > 0
begin
select @temp_fraction = @temp_fraction * 2
if @temp_fraction >= 1
begin
select @single_digit = 1
select @temp_fraction = @temp_fraction - 1
end
else
begin
select @single_digit = 0
end
select @fraction_part_0_1 = @fraction_part_0_1 + @single_digit
end
end
else
begin
select @fraction_part_0_1 = '0'
end
---------------------------------------------------------------------
select @integer_fraction_0_1 = @integer_part_0_1 + '.' + @fraction_part_0_1
select @leading_one_position = charindex('1', @integer_fraction_0_1)
select @dot_position = charindex('.', @integer_fraction_0_1)
if @dot_position < @leading_one_position
select @temp_exp = @dot_position - @leading_one_position + 127
else
select @temp_exp = @dot_position - @leading_one_position - 1 + 127
select @integer_fraction_0_1 = replace(@integer_fraction_0_1, '.', '')
select @integer_fraction_0_1 = replace(ltrim(replace(@integer_fraction_0_1, '0', ' ')), ' ', '0') -- remove leading zeros
select @integer_fraction_0_1 = substring(@integer_fraction_0_1, 2, len(@integer_fraction_0_1)) -- remove the first '1'
-- Exponential Part -------------------------------------------------
select @k = 1
while @temp_exp > 1
begin
select @single_digit = @temp_exp % 2
select @temp_exp = @temp_exp / 2
insert into @exp_result values(@k, @single_digit)
select @k = @k + 1
end
insert into @exp_result values(@k, '1')
select @exp_part_0_1 = ''
while @k > 0
begin
select @single_digit = e_digit
from @exp_result
where e_sn = @k
select @exp_part_0_1 = @exp_part_0_1 + @single_digit
select @k = @k - 1
end
select @exp_part_0_1 = right('00000000' + @exp_part_0_1, 8)
---------------------------------------------------------------------
-- IEEE 754 Format --------------------------------------------------
select @rtn_IEEE_754_string = left(convert(varchar(32), @sign + @exp_part_0_1 + @integer_fraction_0_1, 2) + '00000000000000000000000000000000', 32)
select @rtn_IEEE_754_binary_codes = convert(binary(32), @rtn_IEEE_754_string, 2)
---------------------------------------------------------------------
set nocount off
return
Zero_Handler:
select @rtn_IEEE_754_string = '00000000000000000000000000000000'
select @rtn_IEEE_754_binary_codes = convert(varbinary(64), @rtn_IEEE_754_string, 2)
set nocount off
return
