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

Tags:

Mysql

Sql