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.

CodePudding user response：

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

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

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.