此 Stored Procedure 可將輸入的 IEEE 754 標準二進制浮點格式 (32 bits) 轉換為十進制數字輸出。
IEEE 754 共分為 S (Sign)、E (Exponent)、M (Mantisa) 三個部分 -
計算方式：以 11000010011100010000000000000000 為例
1) 將此數分為 S、E、M 三部分 (S = 1、E = 10000100、M = 11100010000000000000000)
2) S = 1 得知此數為負數 (若為正數則設定為 0)
3) 將 E 轉換為十進制再減去 127，得到 5 (此為 2 的乘方)。
4) 將 M 的左方加上 "1." (即 1.1110001) 後，將小數點右移 5 位，得到 111100.01。
5) 將 111100.01 轉為十進制，得到 60.25 。
6) 最後結果為 -60.25
程式碼
NOTE 欲複製程式碼的讀者，先將滑鼠指標移至程式碼區，雙擊 (Double-Click) 滑鼠左鍵，此時該區程式碼應會呈現 Highlight 狀態；再按下滑鼠右鍵，於出現的選單中選擇複製。若程式碼未出現 Highlight 狀態，請重整 (Refresh) 本網頁後，再重複上述動作。
if object_id ('usp_IEEE754_Restore') is not null
drop proc usp_IEEE754_Restore
go
create proc usp_IEEE754_Restore @prm_IEEE_754_string varchar(32),
@rtn_decimal_number float output
/*----------------------------------------------------------------------------------+
| Author: Wei-jie Yang |
| Date: 2012/05/10 |
| All Rights Reserved |
| |
| [Description] |
| This stored procedure is used to convert IEEE 754 binary floating number to |
| decimal floating number. |
| |
| [Parameters] |
| (1).INPUT Parameter |
| - @prm_IEEE_754_string: The IEEE 754 number that you want to convert. |
| (2).OUTPUT Parameter |
| - @rtn_decimal_number : Converted decimal number |
| |
| [Usage] |
| declare @return_1 float |
| exec usp_IEEE754_Restore '11000010111100000100000000000000', @return_1 output |
| select @return_1 as Decimal_Mode |
+----------------------------------------------------------------------------------*/
as
declare @abs_binary_exp int,
@add_digit_number tinyint,
@dot_left_digits int,
@dot_position int,
@dot_right_digits int,
@binary_exp int,
@binary_exp_string varchar(8),
@binary_float_string varchar(50),
@binary_float_string_dot_left_part varchar(50),
@binary_float_string_dot_right_part varchar(50),
@binary_float_string_length int,
@decimal_fraction float,
@decimal_integer int,
@digit_counter int,
@i tinyint,
@j tinyint,
@k tinyint,
@shift_string varchar(50),
@single_digit char(1)
-- Binary Exponential -----------------------------------------------
select @binary_exp_string = substring(@prm_IEEE_754_string, 2, 8)
select @binary_exp = 0
select @i = 8
while @i >= 1
begin
select @single_digit = substring(@binary_exp_string, @i, 1)
select @binary_exp = @binary_exp + power(2, 8 - @i) * cast(@single_digit as tinyint)
select @i = @i - 1
end
select @binary_exp = @binary_exp - 127
---------------------------------------------------------------------
-- Binary Float -----------------------------------------------------
select @binary_float_string = '1.' + substring(@prm_IEEE_754_string, 10, 23)
-- Use '.' to split binary string into two parts
select @dot_position = charindex('.', @binary_float_string)
if @dot_position > 0
begin
select @binary_float_string_length = len(@binary_float_string)
select @binary_float_string_dot_left_part = substring(@binary_float_string, 1, @dot_position - 1)
select @binary_float_string_dot_right_part = substring(@binary_float_string, @dot_position + 1, @binary_float_string_length - @dot_position)
end
else
begin
select @binary_float_string_dot_left_part = @binary_float_string
select @binary_float_string_dot_right_part = ''
end
-- Process Binary Exponential
if @binary_exp > 0
begin
select @shift_string = substring(@binary_float_string, @dot_position + 1, @binary_exp)
select @binary_float_string_dot_left_part = @binary_float_string_dot_left_part + @shift_string
if len(@binary_float_string_dot_right_part) = @binary_exp
begin
select @binary_float_string_dot_right_part = ''
end
else
begin
if len(@binary_float_string_dot_right_part) < @binary_exp -- range exceeded
begin
if substring(@prm_IEEE_754_string, 1, 1) = '1'
begin
select @rtn_decimal_number = 1073741824 * -1
goto Error_Handler
end
else
begin
select @rtn_decimal_number = 1073741824
goto Error_Handler
end
end
select @binary_float_string_dot_right_part =
substring(@binary_float_string_dot_right_part, @binary_exp + 1, len(@binary_float_string_dot_right_part) - @binary_exp)
end
end
else if @binary_exp < 0
begin
select @binary_float_string_dot_left_part = ''
select @add_digit_number = 1
select @abs_binary_exp = abs(@binary_exp)
while @add_digit_number <= abs(@binary_exp)
begin
if @add_digit_number = 1
begin
select @binary_float_string_dot_right_part = '1' + @binary_float_string_dot_right_part
end
else
begin
select @binary_float_string_dot_right_part = '0' + @binary_float_string_dot_right_part
end
select @add_digit_number = @add_digit_number + 1
end
end
---------------------------------------------------------------------
-- Generate Decimal Integer -----------------------------------------
select @dot_left_digits = len(@binary_float_string_dot_left_part)
select @decimal_integer = 0
select @j = @dot_left_digits
select @digit_counter = 1
while @j >= 1
begin
select @single_digit = substring(@binary_float_string_dot_left_part, @digit_counter, 1)
select @decimal_integer = @decimal_integer + power(2, @j - 1) * cast(@single_digit as tinyint)
select @j = @j - 1
select @digit_counter = @digit_counter + 1
end
--------------------------------------------------------------------
-- Generate Decimal Fraction ----------------------------------------
select @dot_right_digits = len(@binary_float_string_dot_right_part)
select @decimal_fraction = 0
select @k = 1
while @k <= @dot_right_digits
begin
select @single_digit = substring(@binary_float_string_dot_right_part, @k, 1)
if @single_digit <> 0
begin
if @k <= 30
begin
select @decimal_fraction = @decimal_fraction + (1 / (power (2, @k) * 1.0)) * cast(@single_digit as tinyint)
end
else -- exceed range
begin
select @decimal_fraction = @decimal_fraction + (1 / (power (2, 30) * 1.0)) * cast(@single_digit as tinyint)
end
end
select @k = @k + 1
end
---------------------------------------------------------------------
if substring(@prm_IEEE_754_string, 1, 1) = '1'
select @rtn_decimal_number = (@decimal_integer + @decimal_fraction) * -1
else
select @rtn_decimal_number = (@decimal_integer + @decimal_fraction)
return
