Rounding numbers given in string representation while controlling their number of significant digits

6 Comments

1. gigi December 2, 2008

   This function is wrong for integers of x decimals which are rounded to less than x decimals, e.g. 12345 when rounded to 3 significant decimals is 12300 but should be displayed as 123×100 or whichever fancy notation other than the usual scientific notation 123E+02 one has in mind.
2. gigi December 1, 2008

   Below is the DeLuxe version in which the character version keeps trailing zeroes up to the required precision, e.g. 1.2300 becomes 1.230 and not 1.23 when precision is 4 numbers.

   /* Rounds to the significant number of decimals. */
   PROCEDURE PSM_SIGNIFICANT
   (P_NUMBER IN NUMBER
   ,P_PRECISION IN INTEGER
   ,P_NLS_NUMERIC_CHARACTERS IN VARCHAR2
   ,P_ROUNDED_NUM OUT NUMBER
   ,P_ROUNDED_CHAR OUT VARCHAR2
   )
   IS
   c_max_before_point constant number:=20;
   c_max_after_point constant number:=20;
   c_mask_before_point constant varchar2(2000) := lpad(‘0′,c_max_before_point ,’9′) ; — 999…9990
   — search first significant digit without using logarithm
   — e.g. 0.00012345678 to 1.234567800…00E-04
   c_scientific constant varchar2(2000):=to_char( p_number, c_mask_before_point ||’.’|| RPAD(‘9′,c_max_after_point ,’9′)||’EEEE’);
   — distill the positition of the first decimal other than 0 e.g. -04 where 0 for the decimal
   — immediately before the decimal point or comma (or when p_float is 0)
   c_significance constant integer:=-to_number(substr(c_scientific,instr(c_scientific,’E’)+1));
   c_round constant integer := c_significance+p_precision-1;
   BEGIN

   if p_precision > 0
   then

   — round starting at first significant decimal
   p_rounded_num:=round(p_number,c_round);

   — convert to character
   if p_nls_numeric_characters is null
   then — speed up when not needed
   null;
   else

   if c_round >= 1
   then — significant zeroes are lost when behind decimal point or comma
   p_rounded_char:= to_char(p_rounded_num, c_mask_before_point
   ||’D’||
   rpad(‘0′,c_round,’0′)
   ,’NLS_NUMERIC_CHARACTERS=”’|| p_nls_numeric_characters ||”’ ‘
   );
   else — integer
   p_rounded_char:= to_char(p_rounded_num, c_mask_before_point );
   end if;

   end if;– branch on nls-numeric-characters

   else — ignore

   p_rounded_num :=p_number;
   p_rounded_char:=to_char(p_rounded_num);

   end if;
   END PSM_SIGNIFICANT;

   /* Rounds to the significant number of decimals. */
   FUNCTION PSM_SIGNIFICANT_NUM
   (P_NUMBER IN NUMBER
   ,P_PRECISION IN INTEGER
   )
   RETURN NUMBER
   IS

   v_rounded_num number;
   v_rounded_char varchar2(2000); –dummy

   BEGIN

   psm_significant(p_number => p_number
   ,p_precision => p_precision
   ,p_nls_numeric_characters=> to_char(null)
   ,p_rounded_num => v_rounded_num — returned
   ,p_rounded_char=> v_rounded_char — returned, null
   );

   return v_rounded_num;
   END PSM_SIGNIFICANT_NUM;

   /* Rounds to the significant number of decimals, fractional 0’s included. */
   FUNCTION PSM_SIGNIFICANT_CHAR
   (P_NUMBER IN NUMBER
   ,P_PRECISION IN INTEGER
   ,P_NLS_NUMERIC_CHARACTERS IN VARCHAR2 := ‘.,’
   )
   RETURN VARCHAR2
   IS
   v_rounded_num number; –dummy
   v_rounded_char varchar2(2000);
   BEGIN
   psm_significant(p_number => p_number
   ,p_precision => p_precision
   ,p_nls_numeric_characters=> p_nls_numeric_characters
   ,p_rounded_num => v_rounded_num — returned
   ,p_rounded_char=> v_rounded_char — returned
   );

   return v_rounded_char;
   END PSM_SIGNIFICANT_CHAR;
3. gigi November 28, 2008

   The following function essentially does not manipulate strings and relies on arithmetic instead.

   create or replace function significant(p_float in number, p_precision integer)
   — converts the number p_float to a floating point number rounded to the number of significant decimals as specified by p_precision
   — e.g. 0.00012345678 for precision 4 is rounded to 0,0001235 (whereas a ROUND with precision 4 would return 0.0001.)
   return number
   is
   — search first significant digit without using logarithm
   — e.g. 0.00012345678 to 1.234567800…00E-04
   c_scientific constant varchar2(2000):=to_char( p_float, RPAD(‘9′,20,’9′) ||’.’|| RPAD(‘9′,20,’9′)||’EEEE’);
   — distill the positition of the first decimal other than 0 e.g. -04 where 0 for the decimal immediately before the decimal point or comma (or when p_float is 0)
   c_significance constant integer:=-to_number(substr(c_scientific,instr(c_scientific,’E’)+1));
   v_rounded number;
   begin
   if p_precision > 0
   then
   v_rounded:=round(p_float,c_significance+p_precision-1);
   else — ignore
   v_rounded:=p_float;
   end if;
   return v_rounded;
   end;
4. anton July 3, 2006

   Why can’t the WordPress blog handle something simple like a &lt?

   The generic should be

   create or replace function Str_Round
   ( p_numstr in varchar2
   , p_dig number
   ) return varchar2
   is
   t_num number;
   t_fmt varchar2(100);
   begin
   t_num := round( to_number( p_numstr ), p_dig );
   if abs( t_num ) &lt 1
   then
   t_fmt := ‘FM0’;
   else
   t_fmt := rpad( ‘FM’, 2 + trunc( log( 10, abs( t_num ) ) ), ‘9’ ) || ‘0’;
   /* group separator ??
   t_fmt := replace( replace( t_fmt, ‘9990’, ‘9G990’ ), ‘9999’, ‘9G999’ );
   */
   end if;
   if p_dig &gt 0
   then
   t_fmt := t_fmt || rpad( ‘D’, 1 + p_dig, ‘9’ );
   end if;
   return to_char( t_num, t_fmt );
   end
5. anton July 3, 2006

   Or something like

   create or replace function Str_Round
   ( p_numstr in varchar2
   , p_dig number
   ) return varchar2
   is
   begin
   return to_char( to_number( p_numstr ), ‘FM9999999999999990’ || rpad( ‘.’, 1 + p_dig, ‘9’ ) );
   end

   Or to make it a bit more generic

   create or replace function Str_Round
   ( p_numstr in varchar2
   , p_dig number
   ) return varchar2
   is
   t_num number;
   t_fmt varchar2(100);
   begin
   t_num := round( to_number( p_numstr ), p_dig );
   if abs( t_num ) 0
   then
   t_fmt := t_fmt || rpad( ‘D’, 1 + p_dig, ‘9’ );
   end if;
   return to_char( t_num, t_fmt );
   end
6. Mäx July 2, 2006

   why not just doing it like that?

   alter session set nls_numeric_characters = ‘.,’ ;
   select to_number( to_char( 0.00000345, ‘9.999EEEE’ ) ) from dual ;