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;<br /> v_Int VARCHAR2(30);<br /> v_Frc VARCHAR2(30);<br /> v_Frc1 VARCHAR2(30);<br /> v_Frc2 VARCHAR2(30);<br /> v_Len NUMBER;<br /> v_Ln1 NUMBER;<br /> v_Ln2 NUMBER;<br /> v_Pt NUMBER;<br /> v_Dig NUMBER := p_Dig;<br /> v_Num NUMBER;<br /> v_sgn VARCHAR2(1);<br /><br />BEGIN<br /> --pos or neg?<br /> v_sgn := SubStr(v_numstr,1,1);<br /> IF v_sgn <> '-' THEN<br /> v_sgn := '';<br /> ELSE<br /> -- use abs value of v_numstr in the rest<br /> v_numstr := SubStr(v_numstr,2);<br /> END IF;<br /><br /> v_Pt := Instr(v_Numstr, '.');<br /> v_Len := Length(v_Numstr);<br /><br /> -- correct pathological representations (eg '.99');<br /> IF v_Pt = 1 THEN<br /> v_Numstr := '0' || v_Numstr;<br /> v_len := v_len + 1;<br /> v_pt := 2;<br /> ELSIF v_Pt = v_Len THEN<br /> v_Numstr := v_Numstr || '0';<br /> v_pt := v_pt;<br /> v_len := v_len + 1;<br /> ELSIF v_Pt = 0 THEN<br /> v_Numstr := v_Numstr || '.0';<br /> v_pt := v_len + 1;<br /> v_len := v_len + 2;<br /> END IF;<br /><br /> v_Int := Substr(v_Numstr, 1, v_Pt - 1);<br /> v_Ln1 := Length(v_Int); -- >= 1<br /><br /> v_Frc := Substr(v_Numstr, v_Pt + 1);<br /> v_Ln2 := Length(v_Frc); -- >= 1<br /><br /> IF v_Dig > v_Ln2 THEN<br /> -- no rounding needed; padding of zero's<br /> v_Frc := Rpad(v_Frc, v_Dig, '0');<br /> ELSIF v_Dig = v_Ln2 THEN<br /> -- no action needed<br /> NULL;<br /> ELSE<br /> -- do the actual rounding <br /> v_Frc1 := Substr(v_Frc, 1, v_Dig);<br /> v_Frc2 := Substr(v_Frc, v_Dig + 1);<br /> v_Num := To_Number(v_Int || v_Frc1);<br /><br /> IF To_Number(Ltrim(v_Frc2)) >= Power(10, v_Ln2 - v_Dig) / 2 THEN<br /> v_Num := v_Num + 1; -- an integer<br /> v_Numstr := To_Char(v_Num);<br /> v_Len := Length(v_Numstr);<br /> v_Int := Nvl(Substr(v_Numstr, 1, v_Len - v_Dig),'0'); -- evt,v_len < v_dig!!<br /> v_Frc := Substr(v_Numstr, v_Len - v_Dig + 1);<br /> ELSE<br /> v_Frc := v_Frc1;<br /> END IF;<br /> END IF;<br /><br /> -- test for fractional parts that became void by rounding<br /> IF v_Frc IS NULL THEN<br /> v_Numstr := v_sgn||v_Int;<br /> ELSE<br /> v_Numstr := v_sgn||v_Int ||'.'|| v_Frc;<br /> END IF;<br /><br /> RETURN(v_Numstr);<br />END;
-
Comments are closed
-
#2 written by gigi 4 years ago
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;
BEGINif 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;
elseif 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
ISv_rounded_num number;
v_rounded_char varchar2(2000); –dummyBEGIN
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 written by gigi 4 years ago
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 written by anton 6 years ago
Why can’t the WordPress blog handle something simple like a <?
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 ) < 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 > 0
then
t_fmt := t_fmt || rpad( ‘D’, 1 + p_dig, ’9′ );
end if;
return to_char( t_num, t_fmt );
end -
#5 written by anton 6 years ago
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′ ) );
endOr 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 - Comment Feed for this Post
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.