How to show big numbers per K (stands for kilo)?

Question

I have a table like this:

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

Now I want this output:

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

---

Well, first of all, I want to know, Is it possible to I do that using MySQL? Something like this:

select id, name,
  concat(substr(reputation, 1, 4), IF(LENGTH(reputation) > 4, 'k', '')) as NewRep 
from table

I know the above query is not correct, I just said it as a clue ..!

---

But if implementing that is not possible using MySQL, then how can I do that using PHP?

if (strlen($result['reputation']) >= 4){
    $NewRep = substr($result['reputation'],0,3);
    $NewRep = round($NewRep).'k';
}

However this ^ solution is incomplete. Because it does not support .5 (point half), and also its sbust() does not work as well.

Answer 1

Try this:

Query1:

SELECT id, name, 
   IF(reputation >= 1000,
      CONCAT(
         IF(LENGTH(ROUND(reputation/1000, 1)) > 4,
            ROUND(reputation/1000),
            TRUNCATE(reputation/1000,1)),
      "K"),
   reputation) as reputation  
FROM `table`

Query2: (This is more readable)

SELECT id, name, 
    CASE WHEN reputation >=100000  THEN  CONCAT( ROUND   ( reputation /1000 ),     "k" ) 
         WHEN reputation >=1000    THEN  CONCAT( TRUNCATE( reputation /1000, 1 ),  "k" ) 
         ELSE reputation
    END AS reputation
FROM `table`

Query3: (shorter and faster)

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` 

Output: (for all of queries)

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

Answer 2

I stumbled upon this answer by Renaat De Muynck

and I found this solution

http://sqlfiddle.com/#!9/666e16/2

SELECT
  id,
  CASE 
   WHEN number = 0 THEN 0 
   ELSE 
    CONCAT(
      ROUND(number / POW(1000, TRUNCATE(LOG(1000, number), 0)), 0),
      ' ',
      ELT(TRUNCATE(LOG(1000, number), 0) + 1, '', 'k', 'M', 'G', 'T', 'P', 'E', 'Z', 'Y', 'B')
    )
  END
  AS number
FROM test;

Answer 3

SELECT 
    id,
    name,
    IF(reputation >= 1000,
        CONCAT(IF(LENGTH(LEFT(CAST(reputation / 100 AS CHAR), LENGTH(reputation)-2)) >= 4,
                    LEFT(reputation, LENGTH(reputation)-3),
                    LEFT(CAST(reputation / 1000 AS CHAR), LENGTH(reputation)-1)),
                'k'),
        reputation) AS reputation
FROM
    table

Something like that would probably do it.

Output:

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