Is there a built-in function in MySQL the removes trailing zeros on the right?
I have samples and i want my output to be like this:
1.0 ==> 1
1.50 ==> 1.5
10.030 ==> 10.03
0.50 ==> 0.5
0.0 ==> 0
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`;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With