-- The number of decimal places can be up to 9 digits, and the tenth digit is not displayed.
Create Function to_char (v_value double, v_decimal INT) -- v_value passed in value, v_decimal decimal places (not implemented yet mainly for retaining decimal places)
Returns varchar (128)
Language SQL
Begin atomic
Declare v_result_t varchar (128); -- returned result (temporary variable)
Declare v_result varchar (128); -- returned result
Declare e_index int; -- index location of E
Declare e_last_str varchar (128); -- number behind e
Declare e_last_int; -- number behind e
Declare e_pre_str varchar (128); -- number of front edges of E
Declare dot_index int; -- index location of the decimal point
Declare TMP varchar (128); -- Temporary Variable
Declare e_pre_last char (2); -- the last digit of the e_pre_str variable
Declare last_0 int; -- the number of zeros to be filled back
Declare v_length int; -- String Length
Declare v_pre char (3); -- the first two digits of the string
Declare v_symbol int; -- symbol bit
Set v_result_t = rtrim (ltrim (char (v_value); -- directly changes to a string
Set e_index = posstr (v_result_t, 'E'); -- locate the index location of E.
Set v_symbol = posstr (v_result_t, '-'); -- locate the symbol bit. If it is 1, it indicates a negative number and 0 indicates a positive number.
If (v_symbol = 1) then
Set v_result_t = substr (v_result_t, (v_symbol + 1); -- if the signed value is reset to the unsigned number
Set e_index = e_index-1; -- and reset the index location of E
End if;
If (e_index <= 0) then -- if the index is not e, it indicates that it is an integer or an error is returned.
Return v_result_t;
Else
If (v_result_t = '0e0') then -- adds special processing for 0.
Return char (0 );
End if;
Set e_last_str = substr (v_result_t, (e_index + 1); -- extract the number behind e
Set e_pre_str = substr (v_result_t, 1, (e_index-1); -- extract the number of front edges of E
Set dot_index = posstr (e_pre_str, '.'); -- Obtain the decimal point.
Set e_last_int = cast (e_last_str as INT); -- converts the number behind e to int.
If (e_last_int <0) then -- if E is a negative number, the original number is a decimal number.
Set TMP = '';
While (e_last_int <0) Do
Set TMP = TMP | '0 ';
Set e_last_int = e_last_int + 1;
End while;
Set e_pre_str = Replace (e_pre_str ,'.','');
Set v_result = TMP | e_pre_str;
Set v_result = insert (v_result, 2, 0 ,'.');
If (v_symbol = 1) then
Set v_result = '-' | v_result;
End if;
Return v_result;
Elseif (e_last_int = 0) then -- if e is 0, it is an integer or an integer decimal.
Set e_pre_last = substr (e_pre_str, length (e_pre_str); -- obtain the first digit of E.
If (e_pre_last = '0') then -- if the previous digit of E is 0, the original number is an integer.
Set v_result = substr (e_pre_str, 1, (dot_index-1 ));
Else
Set v_result = e_pre_str;
End if;
If (v_symbol = 1) then
Set v_result = '-' | v_result;
End if;
Return v_result;
Else -- if e is greater than 0, the scientific notation is used to move the decimal point according to the extended multiple of E.
Set e_pre_str = Replace (e_pre_str, '.', ''); -- remove the decimal point
Set last_0 = (dot_index + e_last_int)-length (e_pre_str)-1); -- calculate the number of zeros to be supplemented after
While (last_0> 0) Do
Set e_pre_str = e_pre_str | '0 ';
Set last_0 = last_0-1;
End while;
Set v_result_t = insert (e_pre_str, (dot_index + e_last_int), 0 ,'.');
-- Handle unreasonable values (for example, 35. | 000.123)
Set v_length = length (v_result_t );
Set dot_index = posstr (v_result_t ,'.');
If (v_length = dot_index) then
Set v_result_t = Replace (v_result_t ,'.','');
End if;
If (v_length> = 2) then
Set v_pre = substr (v_result_t, 1, 2 );
If (v_pre = '00') then
Set v_result = '0' | substr (v_result_t, posstr (v_result_t ,'.'));
Else
Set v_result = v_result_t;
End if;
End if;
If (v_symbol = 1) then
Set v_result = '-' | v_result;
End if;
Return v_result;
End if;
End if;
End;
I have written a version before, but it is a problem due to weeks of consideration.
Modified this time and added a negative number conversion.
The version is modified again, the bug is fixed, and 0 is added,