Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
917 views
in Technique[技术] by (71.8m points)

mysql - Remove trailing zeros in decimal value with changing length

I have a procedure I am doing that displays odds but the client wants only significant digits to be shown. So, 1.50 would show as '1.5' and 1.00 would show as '1'.

How can I get MySQL to not display trailing zeros;

i.e. in the database:

Odds
1.500
23.030
2.000
4.450

would display as

1.5
23.03
2
4.45

Thanks for any help

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...