CODEWITHSUNDEEP
mysqlsql
Martin AJ
Martin AJ

Reputation: 6697

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?

Upvotes: 1

Views: 279

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 1

Martin AJ
Martin AJ

Reputation: 6697

I can use CASE WHEN and TRAILING like this:

SELECT id, name,
    CASE WHEN value >= 1000 THEN
         CONCAT(TRIM(TRAILING '.' FROM SUBSTR(TRUNCATE(number/1000, 1), 1, 4)), 'k')
         ELSE value
    END as reputation
FROM `table`

Upvotes: 2

Related Questions