MySQL 随着长度的变化删除十进制值中的尾随零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7968531/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove trailing zeros in decimal value with changing length
提问by Chris Morgan
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'.
我正在执行一个显示赔率的程序，但客户只希望显示有效数字。因此，1.50 将显示为“1.5”，而 1.00 将显示为“1”。
How can I get MySQL to not display trailing zeros;
如何让 MySQL 不显示尾随零；
i.e. in the database:
即在数据库中：
Odds
1.500
23.030
2.000
4.450
赔率
1.500
23.030
2.000
4.450
would display as
将显示为
1.5
23.03
2
4.45
1.5
23.03
2
4.45
Thanks for any help
谢谢你的帮助
采纳答案by Marc B
回答by Christopher McGowan
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 DECIMALor NUMERICtype, then cast it to string first to make sure the conversion takes place...ex:
如果您的值来自DECIMAL或NUMERIC类型的列，则首先将其转换为字符串以确保发生转换...例如：
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`;
回答by fooquency
EDIT: I would use the answer below by Christopher McGowan instead - adding 0 to the value, which is better, instead.
编辑：我会改用克里斯托弗·麦高恩 (Christopher McGowan) 的以下答案 - 将 0 添加到值中，这样更好。
It's important to check there is actually a decimal point if doing trimming.
如果进行修整，检查实际上是否有小数点很重要。
So I think you'd want to use:
所以我认为你想使用：
SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from yourfield)) AS yourfield
FROM yourtable
WHERE yourfield LIKE '%.%'
回答by PodTech.io
this worked for me.. round the field to 2 decimal places and then trim any trailing zeros
这对我有用..将字段舍入到小数点后两位，然后修剪任何尾随零
So that 2.10 is 2.1
所以 2.10 是 2.1
SELECT trim(round(FIELDNAME,2))+0
FROM tbl_name
....
回答by user1409935
Please use below function , it will take care of number having zero without decimal places i.e 150 etc....
请使用下面的功能，它会处理没有小数位的零数字，即150等......
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
DELIMITER $$
USE `mydbname`$$
DROP FUNCTION IF EXISTS `FN_STRIP_TRAILING_ZER0`$$
CREATE DEFINER=`mydbuser`@`%` FUNCTION `FN_STRIP_TRAILING_ZER0`(tNumber DECIMAL(10,7)) RETURNS VARCHAR(20) CHARSET utf8
BEGIN
DECLARE strBuff VARCHAR(20);
DECLARE cnt NUMERIC(2);
DECLARE tString VARCHAR(20);
SELECT CAST(tNumber AS CHAR) INTO tString;
SELECT LOCATE('.',tString) INTO cnt;
IF cnt > 0 THEN
SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM tString)) INTO strBuff;
ELSE
SET strBuff = tString;
END IF;
RETURN strBuff;
END$$
DELIMITER ;
SET character_set_client = @saved_cs_client;
Addendum:
附录：
Typically to call this would involve: SELECT FN_STRIP_TRAILING_ZER0(1.5);
通常调用它会涉及： SELECT FN_STRIP_TRAILING_ZER0(1.5);
回答by mohitp
Taking forward the answer provided by @fooquency, if the column is already declared as a DECIMAL with a non-zero value for D in DECIMAL(M, D), we do not need to perform the WHERE condition
转发@fooquency 提供的答案，如果该列已经声明为 DECIMAL，DECIMAL(M, D) 中的 D 值为非零，则我们不需要执行 WHERE 条件
WHERE yourfield LIKE '%.%'
as the values in the column will always contain D digits after the decimal dot (.)
因为列中的值将始终在小数点 (.) 后包含 D 位数字
回答by Mathieu
The best solution I found is to cast your round value to FLOAT:
我发现的最佳解决方案是将您的回合值转换为 FLOAT：
SELECT CAST(ROUND(1.2345984372, 2) AS FLOAT)
回答by Devner
Here's what worked for me:
以下是对我有用的内容：
SINGLE COLUMN:
单列：
SELECT TRIM(column_name)+0 AS column_name FROM table_name;
MULTIPLE COLUMNS:
多列：
SELECT
TRIM(column1)+0 AS column1,
TRIM(column2)+0 AS column2,
TRIM(column3)+0 AS column3,
FROM table_name;
回答by wellsantos
Taking fragments of the others answers in this page I came to this conclusion:
从本页其他答案的片段中我得出了这个结论：
SELECT ( IF(
myfield LIKE '%.%',
TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM myfield)),
myfield
) ) FROM mytable
Cheers
干杯
回答by Gayan Sandamal
Try this simply updatedQuery to remove decimal zeros
试试这个简单更新的查询来删除十进制零
SELECT TRIM(TRAILING '.00' FROM price_column)
FROM table_name
回答by Ron Ashrovy
Using ROUND or CEILING, in the query you just have to type:
使用 ROUND 或 CEILING，您只需在查询中键入：
SELECT ROUND(2/50)
or
或者
SELECT CEILING(2/50)
