Remove trailing zeros only from int numbers

Question

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

Answer 1

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