具有高精度和小数位数的十进制创建'Arithmetic Overflow Error'

Question

我使用BCP将大量数据导入到包含所有字符串的SQL Server中。为了保持数据库的进一步完整性，我正在为正确的元数据更改所有列。

我有一个当前字符串列，它实际上充满了十进制值，需要将该列更改为十进制(p, s)。

由于数据量大，我编写了以下查询来获取小数点前后字符串的最大长度;从十进制前后添加字符串的长度以获得精度。下面的查询将显示我到达的结果:

SELECT 
    '('
    +
    CAST(MAX(LEN(LEFT(Measure, LEN(Measure) - LEN(SUBSTRING(Measure, CHARINDEX('.', Measure) , 255))))) 
    + 
    MAX(LEN(SUBSTRING(Measure, CHARINDEX('.', Measure) +1, 255))) AS varchar(5)) 
    + 
    ',' 
    + CAST(MAX(LEN(SUBSTRING(Measure, CHARINDEX('.', Measure) +1, 255))) AS varchar(5))
    +
    ')' AS 'max[p,s]'
FROM
    Result

结果如下:

max[p,s]
--------
(24,19)

当我用以下语句修改列时:

ALTER TABLE Result
ALTER COLUMN Measure DECIMAL (24,19)

我得到以下错误:

Msg 8115, 16层，8状态，1行算术溢出错误将varchar转换为数字数据类型。声明是终止。

(p,s) where p = precision and s=scale;我的印象是，我可以有38的最大精度(p)。为什么会出现这个错误?有什么建议吗?

Answer 1

您可以尝试使用以下命令查找错误行:

select Measure from Result 
where TRY_CONVERT(DECIMAL(24,19),Measure)  
is Null And Measure is Not Null;

Answer 2

From MSDN decimal and numeric (Transact-SQL)

p(精度)在小数点的左边和右边存储的十进制位数的最大总数。精度必须在1到38之间取值。默认精度为18。

使用

获取精度

SELECT  MAX(LEN(Measure)) AS max_precision_needed 
FROM    Result

上面的查询没有减去小数点，因为字符串可能包含一个根本没有小数点的整数。它给你可能需要的最大值。

的例子:数字123.00需要一个精度如下的字段;小数(5,2)

在下面的示例中，在计算所需的精度后，可以进行转换。

   DECLARE @measures TABLE ( measure VARCHAR(50) );
   INSERT   INTO @measures( measure ) VALUES   ( '123' )
   INSERT   INTO @measures( measure ) VALUES   ( '123.00' )
   INSERT   INTO @measures( measure ) VALUES   ( '123.001' )
   INSERT   INTO @measures( measure ) VALUES   ( '.123' )
   INSERT   INTO @measures( measure ) VALUES   ( '0.123' )
   INSERT   INTO @measures( measure ) VALUES   ( '   ' )
   SELECT   '(' + CAST(MAX(LEN(measure)) - 1 AS VARCHAR(5)) + '.' + CAST(MAX(LEN(SUBSTRING(measure, CHARINDEX('.', measure) + 1, 255))) AS VARCHAR(5)) + ')'
   FROM     @measures
   SELECT   ISNULL(CONVERT(DECIMAL(6, 3), CASE WHEN CHARINDEX('.', measure) = 0 THEN measure + '.0' ELSE measure END), 0.0)
   FROM     @measures

在本例中，转换不应因字符串中的整数或空字符串而失败。