Idea Summary
In certain situations you want to show the magnitude of a numeric value in a compact form using at most three digits for the integer part, an optional decimal separator, up to three digits for the fractional part, then a character like K for thousands, M for millions, G for billions and T for thousands of billions.
The new format model should cater also for “byte-wise” representations, that is using K for multiples of 1024 instead of 1000, and so on.
Use Case
in SQL Developer DBA instance viewer, many values are represented in a compact form, 1.2K or 34.2M and so on, it would be useful to adopt a similar representation in APEX as well when you are interested in the magnitude of the figures rather than their exact value.
Preferred Solution (Optional)
Ideally the new format model should be represented by K or KB, hence the number 512 would be represented as 0.512K if “K” is specified or 0.5K if “KB” is specified without the optional trailing fractional digits. If the fractional digits are specified, then 0.512 becomes 0.51K when “K2” is specified or 0.50K if “KB2” is specified.
1000000 becomes 1M if “K” is specified or 976.6K if “KB” is specified.
Whether there should be a non-breaking space between the number and the magnitude indicator is up to you, may be a modifier could be added like “_K”.
Below a sample function returning the formatted values as explained above:
create or replace function magnitude_format (
p_number in number,
p_model in varchar2
) return varchar2
as
l_fmt varchar2(50 byte);
l_sep char(1 char);
begin
if regexp_like(p_model, '^_?KB?[0123]?$', 'i') then
if p_number is null then
return null;
else
if substr(p_model,1,1) = '_' then
l_sep := unistr('\00A0');
end if;
if regexp_like(p_model, '^_?KB?$','i') then
l_fmt := 'TM9';
else
case regexp_substr(p_model, '^_?KB?([0123])$',1,1,'i',1)
when '0'
then l_fmt := 'FM999999999999999';
when '1'
then l_fmt := 'FM999999999999999D0';
when '2'
then l_fmt := 'FM999999999999999D00';
when '3'
then l_fmt := 'FM999999999999999D000';
end case;
end if;
case
when regexp_like(p_model, '^_?K[0123]?$', 'i') then
if abs(p_number) != 0 then
case floor(log(1000,abs(p_number)))
when 0 then return to_char(p_number, l_fmt);
when 1 then return to_char(round(p_number/1000, 3), l_fmt) || l_sep ||'K';
when 2 then return to_char(round(p_number/1000000, 3), l_fmt) || l_sep ||'M';
when 3 then return to_char(round(p_number/1000000000, 3), l_fmt) || l_sep ||'G';
else return to_char(round(p_number/1099511627776, 4), l_fmt) || l_sep ||'T';
end case;
else
return to_char(p_number, l_fmt);
end if;
when regexp_like(p_model, '^_?KB[0123]?$', 'i') then
if abs(p_number) != 0 then
case floor(log(1024,abs(p_number)))
when 0 then return to_char(p_number, l_fmt) || l_sep ||'B';
when 1 then return to_char(round(p_number/1024, 1), l_fmt) || l_sep ||'KB';
when 2 then return to_char(round(p_number/1048576, 2), l_fmt) || l_sep ||'MB';
when 3 then return to_char(round(p_number/1073741824, 3), l_fmt) || l_sep ||'GB';
else return to_char(round(p_number/1099511627776, 4), l_fmt) || l_sep ||'TB';
end case;
else
return to_char(p_number, l_fmt) || l_sep ||'B';
end if;
end case;
end if;
else
raise_application_error(-20001,'invalid format');
end if;
end magnitude_format;