如何计算 SQL 中的小数位数? [英] How do I count decimal places in SQL?

问题描述

我有一个 X 列，其中充满了浮点数，小数位数从 0(无小数)到 6(最大值)不等.我可以依靠这样一个事实，即没有大于 6 位小数的浮点数.鉴于此，我如何创建一个新列以告诉我小数点后有多少位?

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?

我看到一些线程建议我使用 CAST 将浮点数转换为字符串，然后解析字符串以计算小数点后字符串的长度.这是最好的方法吗?

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?

推荐答案

你可以这样使用:

declare @v sql_variant

set @v=0.1242311

select SQL_VARIANT_PROPERTY(@v, 'Scale') as Scale

这将返回 7.

我试图使上述查询与 float 列一起工作，但无法按预期工作.它仅适用于 sql_variant 列，如您在此处看到的:http:///sqlfiddle.com/#!6/5c62c/2

I tried to make the above query work with a float column but couldn't get it working as expected. It only works with a sql_variant column as you can see here: 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

这是一个用于测试的 SQL Fiddle:http://sqlfiddle.com/#!6/23d4f/29

Here's a SQL Fiddle to test this out: 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

这是随附的 SQL Fiddle:http://sqlfiddle.com/#!6/10d54/11

Here's the accompanying SQL Fiddle: http://sqlfiddle.com/#!6/10d54/11

这篇关于如何计算 SQL 中的小数位数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！