如何获取ms sql中float列中小数点后的位数？

I have to count the digits after the decimal point in a database hosted by a MS Sql Server (2005 or 2008 does not matter), in order to correct some errors made by users. I have the same problem on an Oracle database, but there things are less complicated. Bottom line is on Oracle the select is:

我必须计算由MS Sql Server(2005或2008无关紧要)托管的数据库中小数点后的数字,以便纠正用户所犯的一些错误。我在Oracle数据库上遇到了同样的问题,但事情并不复杂。底线是关于Oracle的选择是:

select length( substr(to_char(MY_FIELD), instr(to_char(MY_FILED),'.',1,1)+1, length(to_char(MY_FILED)))) as digits_length
from MY_TABLE

where the filed My_filed is float(38).

提交的My_filed是浮点数(38)。

On Ms Sql server I try to use:

在Ms Sql服务器上我尝试使用:

select LEN(SUBSTRING(CAST(MY_FIELD AS VARCHAR), CHARINDEX('.',CAST(MY_FILED AS VARCHAR),1)+1, LEN(CAST(MY_FIELD AS VARCHAR)))) as digits_length
from MY_TABLE

The problem is that on MS Sql Server, when i cast MY_FIELD as varchar the float number is truncated by only 2 decimals and the count of the digits is wrong. Can someone give me any hints?

问题是在MS Sql Server上,当我将MY_FIELD转换为varchar时,浮点数仅被截断2位小数并且数字的计数错误。有人可以给我任何提示吗?

Best regards.

4 个解决方案

#1

SELECT 
LEN(CAST(REVERSE(SUBSTRING(STR(MY_FIELD, 13, 11), CHARINDEX('.', STR(MY_FIELD, 13, 11)) + 1, 20)) AS decimal)) 
from TABLE

#2

I have received from my friend a very simple solution which is just great. So I will post the workaround in order to help others in the same position as me.

我从朋友那里得到了一个非常简单的解决方案。所以我会发布解决方法,以帮助其他人和我一样。

First, make function:

一,制作功能:

create FUNCTION dbo.countDigits(@A float) RETURNS tinyint AS
BEGIN
declare @R tinyint
IF @A IS NULL 
   RETURN NULL
set @R = 0
while @A - str(@A, 18 + @R, @r) <> 0
begin
   SET @R = @R + 1
end
RETURN @R
END
GO

Second:

select MY_FIELD,
dbo.countDigits(MY_FIELD) 
from MY_TABLE

Using the function will get you the exact number of digits after the decimal point.

使用该函数将获得小数点后的确切位数。

#3

The first thing is to switch to using CONVERT rather than CAST. The difference is, with CONVERT, you can specify a format code. CAST uses whatever the default format code is:

首先是切换到使用CONVERT而不是CAST。不同的是,使用CONVERT,您可以指定格式代码。 CAST使用默认格式代码:

When expression is float or real, style can be one of the values shown in the following table. Other values are processed as 0.

当expression是float或real时,style可以是下表中显示的值之一。其他值处理为0。

None of the formats are particularly appealing, but I think the best for you to use would be 2. So it would be:

没有一种格式特别吸引人,但我认为你最好使用的格式是2.所以它会是:

CONVERT(varchar(25),MY_FIELD,2)

This will, unfortunately, give you the value in scientific notation and always with 16 digits e.g. 1.234567890123456e+000. To get the number of "real" digits, you need to split this number apart, work out the number of digits in the decimal portion, and offset it by the number provided in the exponent.

遗憾的是,这将为您提供科学记数法的价值,并且总是带有16位数字,例如1.234567890123456e + 000。要获得“真实”数字的数量,您需要将此数字拆分,计算小数部分中的位数,并将其与指数中提供的数字相抵消。

---

And, of course, insert usual caveats/warnings about trying to talk about digits when dealing with a number which has a defined binary representation. The number of "digits" of a particular float may vary depending on how it was calculated.

当然,在处理具有已定义二进制表示的数字时,插入关于尝试讨论数字的常见警告/警告。特定浮点数的“数字”数可能会有所不同,具体取决于计算方式。

#4

I'm not sure about speed. etc or the elegance of this code. it was for some ad-hoc testing to find the first decimal value . but this code could be changed to loop through all the decimals and find the last time a value was greater than zero easily.

我不确定速度。等或此代码的优雅。这是为了一些临时测试找到第一个十进制值。但是这段代码可以改为循环遍历所有小数,并且可以轻松找到最后一次大于零的值。

DECLARE @NoOfDecimals       int             = 0 
Declare @ROUNDINGPRECISION  numeric(32,16)  = -.00001000

select @ROUNDINGPRECISION = ABS(@ROUNDINGPRECISION)
select @ROUNDINGPRECISION = @ROUNDINGPRECISION - floor(@ROUNDINGPRECISION)


while @ROUNDINGPRECISION < 1
Begin
    select @NoOfDecimals = @NoOfDecimals +1
    select @ROUNDINGPRECISION =  @ROUNDINGPRECISION * 10

end;

select @NoOfDecimals