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

问题描述

我有一个满X的浮点数的小数点，范围从0（无小数）到6（最大）。我可以指望，没有浮点数超过6位小数的事实。鉴于此，如何创建一个新的列，以便告诉我在小数点后有多少数字？

我已经看到一些线程提示我使用CAST转换将浮点数转换为字符串，然后解析字符串以计算小数点后面的字符串的长度。这是最好的方法吗？

解决方案

您可以使用像这样的东西：

  declare @v sql_variant 
 $ b $ set @ v = 0.1242311 
 
选择SQL_VARIANT_PROPERTY（@v，'Scale'）as比例
  

这将返回 7 p>

---

我尝试使用 float 列进行上述查询，但是无法按预期工作。它只适用于 sql_variant 列，你可以在这里看到： http://sqlfiddle.com/#!6/5c62c/2

所以，我开始寻找另一种方法，并在此基础上回答，我得到了这个：

 <$ 
 CAST（
 CAST（
 REVERSE（
 CONVERT（VARCHAR（50），value，128）
 ）AS float 
）as bigint 
）
）as Decimals 
 FROM Numbers 
  

这是一个SQL小提琴来测试： http：// sqlfiddle。 com /＃！6 / 23d4f / 29

---

为了说明这个小怪癖，这是一个修改版本这将处理的情况下，当浮点值没有小数部分：

  
 
 
 
 
 
 
 
 
 
 $ Cast（
 Reverse（CONVERT（VARCHAR（50），value，128））AS FLOAT 
）AS BIGINT 
）
）
 END 
 FROM numbers 
  

下面是附带的SQL小提琴： http://sqlfiddle.com/#!6/10d54/11

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?

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?

解决方案

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.

---

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

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

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

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