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

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

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;

 

 

 

 

 

 

 

 

 

 

 

6 Comments

  1. gigi December 2, 2008
  2. gigi December 1, 2008
  3. gigi November 28, 2008
  4. anton July 3, 2006
  5. anton July 3, 2006
  6. Mäx July 2, 2006