2

For example:

num          result
---------------------
        10    10       
       100   100   
      1000   1k   
     10000   10k   
    100000   100k   
   1000000   1M   
  10000000   10M   
 100000000   100M   
1000000000   1000M
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ravi Kumar
  • 81
  • 11
  • 2
    There are few answers hanging around e.g. https://dba.stackexchange.com/a/84654 or https://stackoverflow.com/questions/19522582/1000000-to-1m-and-1000-to-1k-in-oracle-query It depends which sql server are you looking for. – psimekjr May 27 '17 at 06:31
  • Sql Server 2008 or Above – Ravi Kumar May 27 '17 at 06:44

1 Answers1

2
select num, 
    case 
        when num / 1000000 >= 1 then cast(cast(num / 1000000 as varchar(10)) + 'M' as varchar(10)) 
        when num / 1000 >= 1 then cast(cast(num / 1000 as varchar(10)) + 'K' as varchar(10)) 
        else cast(num as varchar(10)) end as result
from table

Output:

+------------+--------+
|    num     | result |
+------------+--------+
|         10 | 10     |
|        100 | 100    |
|       1000 | 1K     |
|      10000 | 10K    |
|     100000 | 100K   |
|    1000000 | 1M     |
|   10000000 | 10M    |
|  100000000 | 100M   |
| 1000000000 | 1000M  |
+------------+--------+
Max Szczurek
  • 4,324
  • 2
  • 20
  • 32