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;
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.
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;
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;
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
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
why not just doing it like that?
alter session set nls_numeric_characters = ‘.,’ ;
select to_number( to_char( 0.00000345, ‘9.999EEEE’ ) ) from dual ;