CODEWITHSUNDEEP
mysqlphpquery
whiskey_
whiskey_

Reputation: 23

Remove trailing zeros only from int numbers

I have decimal column in db and trying to remove trailing zeros only from int numbers (100, 200, 300) with query in php. I tried with trim/cast functions but 10.50 turned 10.5.

SELECT TRIM(TRAILING '0' FROM `my_column`) FROM `mytable`

200.00

10.50

247.09

would display as

200

10.50

247.09

Upvotes: 0

Views: 545

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35563

Use a case expression to determine if a number is an integer using ceil() and if it is format with zero decimals, if not with 2 decimals.

SELECT
      CASE WHEN ceil(n) = n THEN format(n, 0) ELSE format(n, 2) END
FROM (
      SELECT
            1234.5 AS n
      UNION ALL
      SELECT
            1234
    ) d

Upvotes: 1

Related Questions