Martin AJ Martin AJ - 4 months ago 7
SQL Question

How can I make an abbreviation from the number?

Here is my current table:

// table
+----+--------+------------+
| id | name | reputation |
+----+--------+------------+
| 1 | jack | 534431 |
| 2 | peter | 334 |
| 3 | amos | 1300 |
| 4 | carter | 13490 |
| 5 | basil | 1351 |
+----+--------+------------+


I want to change the value of
reputation
column to a number based on kilo. Actually I'm trying to make it shorter. Exactly what stackoverflow does. So the expected output is something like this:

// newtable
+----+--------+------------+
| id | name | reputation |
+----+--------+------------+
| 1 | jack | 534k |
| 2 | peter | 334 |
| 3 | amos | 1.3k |
| 4 | carter | 13.4k |
| 5 | basil | 1.3k |
+----+--------+------------+


How can I do that?

Answer

You can use concat(), case, and some arithmetic:

select id, name,
       (case when reputation < 1000 then cast(reputation as char)
             when reputation < 1000000 then concat(cast(floor(reputation/1000) as char), 'k')
             when reputation < 1000000000 then concat(cast(floor(reputation/1000000) as char), 'M')
             else 'Wow!!!'
        end) as reputation
. . .

EDIT:

To get the decimal point for some values:

select id, name,
       (case when reputation < 1000 then format(reputation, 0)
             when reputation < 100000 then concat(format(reputation/1000, 2), 'k')
             when reputation < 1000000 then concat(format(reputation/1000, 0), 'k')
             when reputation < 100000000 then concat(format(reputation/1000000, 1), 'M')
             when reputation < 1000000000 then concat(format(reputation/1000000, 0), 'M')
             else 'Wow!!!'
        end) as reputation
. . .

Here is a SQL Fiddle.

Comments