获取最小和最大小数位数

I have float datatype column in sql server and I need to get minimum decimal scale and maximum decimal scale .

我在sql server中有浮点数据类型列，我需要得到最小十进制数和最大十进制数。

下面是我正在使用的SQL Fiddle

Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), value, 128)
                     ) AS FLOAT) AS BIGINT))

Which work great but when value is something in negative eg -0.061 it throw error Error converting data type varchar to float. So for alter I search and found that there is function name PARSENAME(@var,1) which is working perfect for negative numbers too but this function returns after decimal value and return type is nchar

它工作得很好，但当值为负值时，它会抛出错误错误，将数据类型varchar转换为浮点数。因此，对于alter I search，我发现有一个函数名PARSENAME(@var,1)，它也适用于负数，但是这个函数在十进制值后返回，返回类型为nchar。

Eg : If float value is -0.061 its will return 061 of return type nchar So i can not use len function to get its length.

如果浮点值为-0.061，它将返回返回类型为nchar的061，因此我不能使用len函数来获取它的长度。

SO how do I make this working either by using fiddle or by using above function.

那么我如何通过使用小提琴或者使用上面的函数来让它工作呢。

4 个解决方案

#1

Try using the ABS() function

尝试使用ABS()函数。

SELECT value,
       Decimals = CASE Charindex('.', value)
                    WHEN 0 THEN 0
                    ELSE
   Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), ABS(value), 128)
                     ) AS FLOAT) AS BIGINT))
                  END
FROM   numbers

#2

I guess using ABS() function should be enough. Shouldn't it?

我想使用ABS()函数就足够了。不应该吗?

A mathematical function that returns the absolute (positive) value of the specified numeric expression.

返回指定数值表达式的绝对值的数学函数。

SELECT value
    , Decimals = CASE CHARINDEX('.', value)
        WHEN 0 THEN 0
        ELSE LEN(CAST(CAST(REVERSE(CONVERT(VARCHAR(50), ABS(value), 128)) AS FLOAT) AS BIGINT))
    END
FROM numbers;

#3

This will work. Just replace simple value with -value when it is negative i.e CASE WHEN value<0 THEN -value ELSE value END

这将工作。当它是- i时用-value替换简单的值。当值<0时，e的情况为-value值结束。

   SELECT value,
          Decimals = CASE WHEN Charindex('.', value)=0
                          THEN 0
                          ELSE Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), ABS(value), 128) ) AS FLOAT) AS BIGINT))
                     END
   FROM   numbers

#4

To remove - symbol, use ABS function. ABS(value) in place of value

要删除符号，请使用ABS功能。ABS(价值)代替价值。

#1