1000 = 1k

I found this bit of code when I was trying to write a report for work. I like it and wanted to save it as sometimes links seem to go away. So here it is for the second time.

Thanks to Vincent Malgrat on StackOverflow for this.
( http://stackoverflow.com/questions/19522582/1000000-to-1m-and-1000-to-1k-in-oracle-query )

From Oracle documentation: e or E indicates that the number is specified in scientific notation. The digits after the E specify the exponent. The exponent can range from -130 to 125.

WITH data
     AS (SELECT POWER(10, ROWNUM) num
           FROM dual
         CONNECT BY LEVEL <= 9)
SELECT num
     , CASE
         WHEN num >= 1e6 THEN ROUND(num / 1e6) || 'M' -- 1e6 Exponentiation 
         WHEN num >= 1e3 THEN ROUND(num / 1e3) || 'k'
         ELSE TO_CHAR(num)
       END conv
  FROM data;