Oracle Display Number

Question

We need to take some very large numbers and display them in an abbreviated fashion like this:

2113546998.37   --> 21.37B
15481063.31  --> 15.31M

And so on. I do not think Oracle has a method for doing this. Was hoping for some help.

Answer 1

You could use log and power to manipulate and interpret the value; to get it rounded to decimal places for it's nearest 'large number' bracket:

round(your_number / power(10, 3 * floor(log(10, your_number) / 3)), 2)

And then to get the letter to append, something like:

case 3 * floor(log(10, your_number) / 3)
  when 0 then null when 3 then 'K' when 6 then 'M'
  when 9 then 'B' when 12 then 'T' when 15 then 'Q'
end

and so on, though if you get larger than that you'll have to decide how to distinguish between quadrillion and quintillion.

With some extended sample data, a full query of:

select your_number,
  round(your_number / power(10, 3 * floor(log(10, your_number) / 3)), 2)
  ||
  case 3 * floor(log(10, your_number) / 3)
    when 0 then null when 3 then 'K' when 6 then 'M'
    when 9 then 'B' when 12 then 'T' when 15 then 'Q'
    else 'x'
  end as result
from your_table
order by your_number

gets

YOUR_NUMBER	RESULT
123.456789	123.46
1234.56789	1.23K
12345.6789	12.35K
123456.789	123.46K
1234567.89	1.23M
15481063.31	15.48M
123456789	123.46M
2113546998.37	2.11B
123456789123	123.46B
123456789123456	123.46T

So that gets 2.11B and 15.48M for your two original values, not 21.37B and 15.31M as your question showed - but as pointed out in comments, it wouldn't really make sense to only keep both extremes of the precision. It's possible do do that, of course - floor instead of round, and append the original decimal part - but it seems unlikely that's what you really meant, and I've assume both 21 vs 2 and the decimal parts are mistakes putting the question together.

You might not want to apply it to smaller numbers though - 'K' is perhaps less common? - and if so you could use another case expression to decide. For example:

select your_number,
  case
    when log(10, your_number) < 6
    then to_char(round(your_number, 2))
    else
      round(your_number / power(10, 3 * floor(log(10, your_number) / 3)), 2)
      ||
      case 3 * floor(log(10, your_number) / 3)
        when 6 then 'M' when 9 then 'B' when 12 then 'T' when 15 then 'Q'
        else 'x'
      end
  end as result
from your_table
order by your_number

YOUR_NUMBER	RESULT
123.456789	123.46
1234.56789	1234.57
12345.6789	12345.68
123456.789	123456.79
1234567.89	1.23M
15481063.31	15.48M
123456789	123.46M
2113546998.37	2.11B
123456789123	123.46B
123456789123456	123.46T

Either way you can easily put the logic into a function.

db<>fiddle

I've only looked at positive, non-zero numbers; if you need to handle zero or negative numbers then it will need a little bit more work.