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

6

For a reporting application demanding numeric accuracy we needed a functionality for rounding (positive) numbers given in string representation while controlling their number of significant digits. I found the standard oracle functionality somewhat lacking in this respect since it doesn’t necessarily maintain the number of significant digits, at least when using the standard chain: round(to_number(..)).

The following PL/SQL is our own string rounding function
, based solely on the string representaion of the number. The method used is rather obvious (and mathematically trivial) so I consider the code to be amply self explanatory. The function is fed with the floating point number in string form (p_numstr) and the number of significant digits to round it to (p_dig).

create or replace FUNCTION Str_Round (p_Numstr IN VARCHAR2, p_Dig NUMBER) RETURN VARCHAR2 IS

    v_Numstr VARCHAR2(30) := p_Numstr;
    v_Int    VARCHAR2(30);
    v_Frc    VARCHAR2(30);
    v_Frc1   VARCHAR2(30);
    v_Frc2   VARCHAR2(30);
    v_Len    NUMBER;
    v_Ln1    NUMBER;
    v_Ln2    NUMBER;
    v_Pt     NUMBER;
    v_Dig    NUMBER := p_Dig;
    v_Num    NUMBER;
    v_sgn         VARCHAR2(1);

BEGIN
    --pos or neg?
    v_sgn := SubStr(v_numstr,1,1);
    IF v_sgn <> '-' THEN
        v_sgn := '';
    ELSE
         -- use abs value of v_numstr in the rest
        v_numstr := SubStr(v_numstr,2);
    END IF;

    v_Pt  := Instr(v_Numstr, '.');
    v_Len := Length(v_Numstr);

    -- correct pathological representations (eg '.99');
    IF v_Pt = 1 THEN
        v_Numstr := '0' || v_Numstr;
        v_len := v_len + 1;
        v_pt := 2;
    ELSIF v_Pt = v_Len THEN
        v_Numstr := v_Numstr || '0';
        v_pt := v_pt;
        v_len := v_len + 1;
    ELSIF v_Pt = 0 THEN
        v_Numstr := v_Numstr || '.0';
        v_pt := v_len + 1;
        v_len := v_len + 2;
    END IF;

    v_Int := Substr(v_Numstr, 1, v_Pt - 1);
    v_Ln1 := Length(v_Int); -- >= 1

    v_Frc := Substr(v_Numstr, v_Pt + 1);
    v_Ln2 := Length(v_Frc); -- >= 1

    IF v_Dig > v_Ln2 THEN
        -- no rounding needed; padding of zero's
        v_Frc := Rpad(v_Frc, v_Dig, '0');
    ELSIF v_Dig = v_Ln2 THEN
        -- no action needed
        NULL;
    ELSE
        -- do the actual rounding 
        v_Frc1 := Substr(v_Frc, 1, v_Dig);
        v_Frc2 := Substr(v_Frc, v_Dig + 1);
        v_Num  := To_Number(v_Int || v_Frc1);

        IF To_Number(Ltrim(v_Frc2)) >= Power(10, v_Ln2 - v_Dig) / 2 THEN
            v_Num    := v_Num + 1; -- an integer
            v_Numstr := To_Char(v_Num);
            v_Len    := Length(v_Numstr);
            v_Int    := Nvl(Substr(v_Numstr, 1, v_Len - v_Dig),'0'); -- evt,v_len < v_dig!!
            v_Frc    := Substr(v_Numstr, v_Len - v_Dig + 1);
        ELSE
            v_Frc := v_Frc1;
        END IF;
    END IF;

    -- test for fractional parts that became void by rounding
    IF v_Frc IS NULL THEN
        v_Numstr := v_sgn||v_Int;
    ELSE
        v_Numstr := v_sgn||v_Int ||'.'|| v_Frc;
    END IF;

    RETURN(v_Numstr);
END;

 

 

 

 

 

 

 

 

 

 

 

Share.

About Author

6 Comments

  1. 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. 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. 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. 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. 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. why not just doing it like that?

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