Remove trailing zeros in decimal value with changing length

this worked for me.. round the field to 2 decimal places and then trim any trailing zeros

So that 2.10 is 2.1

SELECT trim(round(FIELDNAME,2))+0 
FROM tbl_name
....

EDIT: I would use the answer below by Christopher McGowan instead - adding 0 to the value, which is better, instead.

---

It's important to check there is actually a decimal point if doing trimming.

So I think you'd want to use:

SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from yourfield)) AS yourfield
FROM yourtable
WHERE yourfield LIKE '%.%'

To remove trailing zeros from a DECIMAL/NUMERIC or string type column, you can simply cast the value to DOUBLE, e.g.:

SELECT CAST(mycol AS DOUBLE) from mytable;

or

SELECT mycol + 0E0 FROM mytable;

In fact, the "cast to char and add zero" trick mentioned in other answers does the same, but in a more indirect (and likely less efficient) way, e.g:

SELECT CAST(mycol AS CHAR)+0 FROM mytable; -- converts to string, then to number
SELECT TRIM(mycol)+0 FROM mytable; -- ditto

Easiest way by far, just add zero!

Examples:

SET 
    @yournumber1="1.500", 
    @yournumber2="23.030",
    @yournumber3="2.000",
    @yournumber4="4.450"
;

SELECT 
    (@yournumber1+0),
    (@yournumber2+0),
    (@yournumber3+0),
    (@yournumber4+0)
;

+------------------+------------------+------------------+------------------+
| (@yournumber1+0) | (@yournumber2+0) | (@yournumber3+0) | (@yournumber4+0) |
+------------------+------------------+------------------+------------------+
|              1.5 |            23.03 |                2 |             4.45 |
+------------------+------------------+------------------+------------------+
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`;