Skip to Main Content
  • Questions

  • how to convert 10000 into 10k in oracle query

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, jawad.

Asked: January 11, 2016 - 10:54 am UTC

Last updated: January 12, 2016 - 12:32 am UTC

Version: apex

Viewed 1000+ times

You Asked

how can i change big amount into like 10000 into 10k or 10M etc

and Chris said...

If you want to shorten large numbers, you can use scientific notation or write a custom conversion function such as:

with nums as (
  select power(10, level) n from dual connect by level <= 10
)
  select n,
  to_char(n, '9.99EEEE') sci, 
  case 
    when n >= 1e9 then
     (n / 1e9) || 'G'
    when n >= 1e6 then
     (n / 1e6) || 'M'
    when n >= 1e3 then
     (n / 1e3) || 'K'
    else to_char(n)
  end formatted
from   nums;

           N SCI        FORMATTED                               
------------ ---------- -----------------------------------------
          10   1.00E+01 10                                       
         100   1.00E+02 100                                      
        1000   1.00E+03 1K                                       
       10000   1.00E+04 10K                                      
      100000   1.00E+05 100K                                     
     1000000   1.00E+06 1M                                       
    10000000   1.00E+07 10M                                      
   100000000   1.00E+08 100M                                     
  1000000000   1.00E+09 1G                                       
 10000000000   1.00E+10 10G

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

can there any built in function

jawad ashfaq, January 11, 2016 - 2:03 pm UTC

can there any built in function
Chris Saxon
January 12, 2016 - 12:32 am UTC

no there is not a (supported) one

num2displaysize

Laurent Schneider, January 11, 2016 - 2:13 pm UTC

select dbms_rcvman.Num2DisplaySize(2000000) from dual ;
1.91M

Unfortunately, it's an internal undocumented package

1024 or 1000

Laurent Schneider, January 13, 2016 - 12:01 pm UTC

Depending if you want to have 1K=1000 or 1K=1024 you could use

round(n/power(1024,trunc(log(1024,n))),1)||substr(' KMGTPEZY',trunc(log(1024,n))+1,1)

or

round(n/power(1000,trunc(log(1000,n))),1)||substr(' KMGTPEZY',trunc(log(1000,n))+1,1)


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.