MySQL Remove Trailing Zero

Question

Is there a built-in function in MySQL that removes trailing zeros to 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 

Answer 1

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 

Answer 2

The easiest way is to simply add a 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 in which your value is used is of type DECIMAL or NUMERIC, then first produce it in a row to make sure that the conversion is ... ex:

 SELECT (CAST(`column_name` AS CHAR)+0) FROM `table_name`; 

For a shorter path, simply use any built-in string function to execute:

 SELECT TRIM(`column_name`)+0 FROM `table_name`; 

Answer 3

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