How to display DECIMAL or FLOAT number without trailing zeroes after dot?

Databases
1

I have a column ‘vat’ which is DECIMAL(4,2). But when I do

SELECT vat FROM mytable;

I get this:

0.00
4.50
7.00

How can I retrieve the numbers without the unnecessary zeroes and decimal points? I cannot simply truncate or round the numbers because the decimal part should be there if necessary. I would need this output:

0
4.5
7

I haven’t found any function to do it in a simple way in MySQL. Any ideas?

2 
select trim(trailing '.' from
              trim(trailing '0' from vat))
                   as vat_string ...
 where vat like '%.%'
3

Thanks, it works. However, I wish there was a simpler way to do this - I can do it easily with floatval() in PHP, I thought there was some equvalent of this in mysql.

4

not sure how it is hard, Rudy showed you the code, it is four lines of code, not all that much if you ask me. It isn’t like you are going to type it out once for every value in your table or something.

5

The code is fine, I’m just curious whether there is a simpler solution.

6

As a scientist, I’d say do not trim off the trailing zeros, it shows the number of significant figures.
As a reader of the output, I’d say the same, it lines up the decimal points for you, making interpretation of the numbers easier.

7

Dr John, there are cases you haven’t probably dreamed of when trimming trailing zeroes is very much desirable :smiley: This number is supposed to represent tax percentage and on the invoice it is supposed to appear without trailing zeroes - they don’t make anything clearer (rather the opposite!) and take up space. Actually, all the values are integers now, I just want to prepare the system for the quite unlikely event when the tax value will have a decimal point. I’ve just noticed that when using FLOAT without specifying number of digits mysql doesn’t add trailing zeroes.

8

don’t use float when dealing with money. stick with decimal and format the output in your host language.

9

I’ve heard already that FLOAT is not good for money because of accuracy loss. Can you explain to me in which cases FLOAT can become problematic? Will my arithmetic operations not be accurate even if I store integers in FLOAT columns? I have tried using simple arithmetics on FLOAT like adding, subtracting, multiplying and dividing and have not noticed any problems, all results were perfectly accurate.

10

that is correct, they will not be

11