如何计算 SQL 中的小数位？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14715141/
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
How do I count decimal places in SQL?
提问by phan
I have a column X which is full of floats with decimals places ranging from 0 (no decimals) to 6 (maximum). I can count on the fact that there are no floats with greater than 6 decimal places. Given that, how do I make a new column such that it tells me how many digits come after the decimal?
我有一个 X 列，它充满了浮点数，小数位数从 0（无小数）到 6（最大值）。我可以指望没有超过 6 位小数的浮点数。鉴于此，我如何创建一个新列，以便告诉我小数点后有多少位数字？
I have seen some threads suggesting that I use CAST to convert the float to a string, then parse the string to count the length of the string that comes after the decimal. Is this the best way to go?
我看到一些线程建议我使用 CAST 将浮点数转换为字符串，然后解析字符串以计算小数点后的字符串长度。这是最好的方法吗？
回答by Leniel Maccaferri
You can use something like this:
你可以使用这样的东西：
declare @v sql_variant
set @v=0.1242311
select SQL_VARIANT_PROPERTY(@v, 'Scale') as Scale
This will return 7.
这将返回7。
I tried to make the above query work with a floatcolumn but couldn't get it working as expected. It only works with a sql_variantcolumn as you can see here: http://sqlfiddle.com/#!6/5c62c/2
我试图使上述查询与float列一起工作，但无法按预期工作。它仅适用于sql_variant您在此处看到的列：http: //sqlfiddle.com/#!6/5c62c/2
So, I proceeded to find another way and building upon this answer, I got this:
所以，我继续寻找另一种方法并建立在这个答案的基础上，我得到了这个：
SELECT value,
LEN(
CAST(
CAST(
REVERSE(
CONVERT(VARCHAR(50), value, 128)
) AS float
) AS bigint
)
) as Decimals
FROM Numbers
Here's a SQL Fiddle to test this out: http://sqlfiddle.com/#!6/23d4f/29
这是一个 SQL Fiddle 来测试这个：http://sqlfiddle.com/#!6/23d4f/29
To account for that little quirk, here's a modified version that will handle the case when the float value has no decimal part:
为了解决这个小怪癖，这里有一个修改版本，它将处理浮点值没有小数部分的情况：
SELECT value,
Decimals = CASE Charindex('.', value)
WHEN 0 THEN 0
ELSE
Len (
Cast(
Cast(
Reverse(CONVERT(VARCHAR(50), value, 128)) AS FLOAT
) AS BIGINT
)
)
END
FROM numbers
Here's the accompanying SQL Fiddle: http://sqlfiddle.com/#!6/10d54/11
这是随附的 SQL Fiddle：http://sqlfiddle.com/#!6/10d54/11
回答by Marlin Pierce
A float is just representing a real number. There is no meaning to the number of decimal places of a real number. In particular the real number 3 can have six decimal places, 3.000000, it's just that all the decimal places are zero.
浮点数只是代表一个实数。实数的小数位数没有意义。特别是实数 3 可以有 6 个小数位，3.000000，只是所有的小数位都为零。
You may have a display conversion which is not showing the right most zero values in the decimal.
您可能有一个显示转换，它没有显示十进制中最右边的零值。
Note also that the reason there is a maximum of 6 decimal places is that the seventh is imprecise, so the display conversion will not commit to a seventh decimal place value.
另请注意，最多 6 位小数的原因是第七位不精确，因此显示转换不会提交到第七位小数位值。
Also note that floats are stored in binary, and they actually have binary places to the right of a binary point. The decimal display is an approximation of the binary rational in the float storage which is in turn an approximation of a real number.
另请注意，浮点数以二进制形式存储，并且它们实际上在二进制小数点右侧具有二进制位置。十进制显示是浮点存储中二进制有理数的近似值，而后者又是实数的近似值。
So the point is, there really is no sense of how many decimal places a float value has. If you do the conversion to a string (say using the CAST) you could count the decimal places. That really would be the best approach for what you are trying to do.
所以重点是，对于浮点值有多少个小数位真的没有意义。如果您转换为字符串（例如使用 CAST），您可以计算小数位。对于您正在尝试做的事情，这确实是最好的方法。
回答by vbaranov
This thread is also using CAST, but I found the answer interesting:
该线程也在使用 CAST，但我发现答案很有趣：
http://www.sqlservercentral.com/Forums/Topic314390-8-1.aspx
http://www.sqlservercentral.com/Forums/Topic314390-8-1.aspx
DECLARE @Places INT
SELECT TOP 1000000 @Places = FLOOR(LOG10(REVERSE(ABS(SomeNumber)+1)))+1
FROM dbo.BigTest
and in ORACLE:
在 ORACLE 中：
SELECT FLOOR(LOG(10,REVERSE(CAST(ABS(.56544)+1 as varchar(50))))) + 1 from DUAL
回答by Marlin Pierce
I answered this before, but I can tell from the comments that it's a little unclear. Over time I found a better way to express this.
我之前回答过这个，但我可以从评论中看出它有点不清楚。随着时间的推移，我找到了一种更好的方式来表达这一点。
Consider pi as
将 pi 视为
(a) 3.141592653590
This shows pi as 11 decimal places. However this was rounded to 12 decimal places, as pi, to 14 digits is
这将 pi 显示为小数点后 11 位。然而，这被四舍五入到 12 位小数，作为 pi，到 14 位是
(b) 3.1415926535897932
A computer or database stores values in binary. For a single precision float, pi would be stored as
计算机或数据库以二进制形式存储值。对于单精度浮点数，pi 将存储为
(c) 3.141592739105224609375
This is actually rounded up to the closest value that a single precision can store, just as we rounded in (a). The next lowest number a single precision can store is
这实际上四舍五入到单个精度可以存储的最接近的值，就像我们在（a）中四舍五入一样。单精度可以存储的下一个最低数字是
(d) 3.141592502593994140625
So, when you are trying to count the number of decimal places, you are trying to find how many decimal places, after which all remaining decimals would be zero. However, since the number may need to be rounded to store it, it does not represent the correct value.
因此，当您尝试计算小数位数时，您正在尝试找到多少个小数位，之后所有剩余的小数位将为零。但是，由于数字可能需要四舍五入才能存储，因此它并不代表正确的值。
Numbers also introduce rounding error as mathematical operations are done, including converting from decimal to binary when inputting the number, and converting from binary to decimal when displaying the value.
数字在进行数学运算时也会引入舍入误差，包括输入数字时从十进制转换为二进制，以及在显示值时从二进制转换为十进制。
You cannot reliably find the number of decimal places a number in a database has, because it is approximated to round it to store in a limited amount of storage. The difference between the real value, or even the exact binary value in the database will be rounded to represent it in decimal. There could always be more decimal digits which are missing from rounding, so you don't know when the zeros would have no more non-zero digits following it.
您无法可靠地找到数据库中数字的小数位数，因为它近似于将其四舍五入以存储在有限的存储量中。实际值之间的差异，甚至数据库中精确的二进制值之间的差异将被四舍五入以用十进制表示。舍入时总是会丢失更多的十进制数字，因此您不知道零后面何时不再有非零数字。
回答by DenisS
Solution for Oracle but you got the idea. trunc() removes decimal part in Oracle.
Oracle 的解决方案，但您明白了。trunc() 删除 Oracle 中的小数部分。
select *
from your_table
where (your_field*1000000 - trunc(your_field*1000000)) <> 0;
The idea of the query: Will there be any decimals left after you multiply by 1 000 000.
查询思路：乘以1 000 000后是否还剩下小数点。
回答by Art
Here's another Oracle example. As I always warn non-Oracle users before they start screaming at me and downvoting etc... the SUBSTRING and INSTRING are ANSI SQL standard functions and can be used in any SQL. The Dual table can be replaced with any other table or created. Here's the link to SQL SERVER blog whre i copied dual table code from: http://blog.sqlauthority.com/2010/07/20/sql-server-select-from-dual-dual-equivalent/
这是另一个 Oracle 示例。正如我总是在非 Oracle 用户开始对我尖叫和投反对票等之前警告他们...... SUBSTRING 和 INSTRING 是 ANSI SQL 标准函数，可以在任何 SQL 中使用。Dual table 可以替换为任何其他 table 或创建。这是我从以下位置复制双表代码的 SQL SERVER 博客的链接：http: //blog.sqlauthority.com/2010/07/20/sql-server-select-from-dual-dual-equivalent/
CREATE TABLE DUAL
(
DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO
The length after dot or decimal place is returned by this query. The str can be converted to_number(str) if required. You can also get the length of the string before dot-decimal place - change code to LENGTH(SUBSTR(str, 1, dot_pos))-1 and remove +1 in INSTR part:
此查询返回点或小数位后的长度。如果需要，可以将 str 转换为_number(str)。您还可以在点小数位之前获取字符串的长度 - 将代码更改为 LENGTH(SUBSTR(str, 1, dot_pos))-1 并删除 INSTR 部分中的 +1：
SELECT str, LENGTH(SUBSTR(str, dot_pos)) str_length_after_dot FROM
(
SELECT '000.000789' as str
, INSTR('000.000789', '.')+1 dot_pos
FROM dual
)
/
SQL>
STR STR_LENGTH_AFTER_DOT
----------------------------------
000.000789 6
You already have answers and examples about casting etc...
你已经有了关于铸造等的答案和例子......
