MySQL Remove Trailing Zero

Question

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

Similar questions: [Remove trailing zeros from decimal in SQL Server](http://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server), [Drop 0 value in decimal places](http://stackoverflow.com/questions/1831466/drop-0-value-in-decimal-places) — Devendra D. Chavan, Dec 23 '11 at 03:15
possible duplicate of [Remove trailing zeros in decimal value with changing length](http://stackoverflow.com/questions/7968531/remove-trailing-zeros-in-decimal-value-with-changing-length) — joran, Feb 24 '14 at 22:01

Christopher McGowan · Answer 1 · 2013-11-21T03:10:36.297

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`;

score 9 · Accepted Answer · answered Jan 18 '12 at 00:04

9

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

answered Jan 18 '12 at 00:04

John Woo

258,903
69
498
492

Doesn't this ultimately reinvent what MySQL's built-in string to numeric type casting does? – Christopher McGowan Jan 31 '15 at 02:17
5

30 will become 3. This is a BAD and WRONG answer and should never have been accepted! Christopher McGowan gives the correct answer. – Jannes Nov 11 '15 at 08:25

score 1 · Answer 3 · answered Apr 24 '20 at 04:37

1

This is my method:

SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM `table`.`column`)) FROM table

answered Apr 24 '20 at 04:37

Quan Chau

11
3

score -1 · Answer 4 · answered Feb 21 '14 at 20:47

-1

I had a similar problem in a situation where I could not modify the code nor the SQL query, but I was allowed to modify the database structure. So I changed the column format from DECIMAL to FLOAT and it solved my problem.

answered Feb 21 '14 at 20:47

Jean-François Beauchamp

5,485
8
43
77

2

That could be a dangerous change. DECIMAL type is used for exact values. FLOAT type is for approximate values. – Robert Brisita Jun 20 '14 at 20:33

MySQL Remove Trailing Zero

4 Answers4