MySQL Remove Trailing Zero
Easiest way by far, just add zero!
Examples:
SET
@yournumber1="1.0",
@yournumber2="1.50",
@yournumber3="10.030",
@yournumber4="0.50",
@yournumber5="0.0"
;
SELECT
(@yournumber1+0),
(@yournumber2+0),
(@yournumber3+0),
(@yournumber4+0),
(@yournumber5+0)
;
+------------------+------------------+------------------+------------------+------------------+
| (@yournumber1+0) | (@yournumber2+0) | (@yournumber3+0) | (@yournumber4+0) | (@yournumber5+0) |
+------------------+------------------+------------------+------------------+------------------+
| 1 | 1.5 | 10.03 | 0.5 | 0 |
+------------------+------------------+------------------+------------------+------------------+
1 row in set (0.00 sec)
If the column your value comes from is DECIMAL or NUMERIC type, then cast it to string first to make sure the conversion takes place...ex:
SELECT (CAST(`column_name` AS CHAR)+0) FROM `table_name`;
For a shorter way, just use any built-in string function to do the cast:
SELECT TRIM(`column_name`)+0 FROM `table_name`;
This is my method:
SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM `table`.`column`)) FROM table
it solves my problem using this:
(TRIM(TRAILING '.' FROM(CAST(TRIM(TRAILING '0' FROM setpoint)AS char)))) AS setpoint
example:
mysql> SELECT testid, designationid, test, measure,
(TRIM(TRAILING '.' FROM(CAST(TRIM(TRAILING '0' FROM setpoint)AS char)))) AS setpoint,
(TRIM(TRAILING '.' FROM(CAST(TRIM(TRAILING '0' FROM tmin)AS char)))) AS tmin,
(TRIM(TRAILING '.' FROM(CAST(TRIM(TRAILING '0' FROM tmax)AS char)))) AS tmax,
FROM tests