将VARCHAR转换为DECIMAL

Question

我想将一个varchar(max)列转换为decimal(10,4)。

当我尝试使用cast或convert时，会出现算术溢出异常。问题在于存储在varchar列中的数据可能具有不同的精度和不同的小数位数。例如，123456789.1234567'、1.12345678或123456.1234等。

对于像123456.1234这样的值，它可以顺利转换，但对于其他值，我遇到了一些问题。

- Biju Thomas

---

你认为123456789.1234567能放进一个DECIMAL(10,4)里面吗？请翻译。 - Aaron Bertrand

它必须是十进制数吗？或者你可以使用浮点数或实数数据类型代替：http://msdn.microsoft.com/en-us/library/ms173773.aspx - Mark Kram

如果内容太长无法适应，则我想要识别它。只需要像 SQL 2012 中的 try_parse 那样的方法，但不幸的是 SQL Server 版本是 2008。 - Biju Thomas

你对其他值有什么问题？ - codingbiz

获取算术溢出错误。 - Biju Thomas

Answer 1

经过测试，我发现问题不是小数位引起的，而是精度 (10) 引起的。

这样做行不通：将 varchar 转换为数字类型时出现算术溢出错误。

DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594'

SELECT CAST(@TestConvert AS DECIMAL(10, 4))

这个有效了

DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594'

SELECT CAST(@TestConvert AS DECIMAL(13, 4))

应该像9个整数+4个浮点数=13个字符一样

- codingbiz

Answer 2

我的解释在代码中。 :)

DECLARE @TestConvert VARCHAR(MAX) = '123456789.1234567'
BEGIN TRY
    SELECT CAST(@TestConvert AS DECIMAL(10, 4))
END TRY
BEGIN CATCH
    SELECT 'The reason you get the message "' + ERROR_MESSAGE() + '" is because DECIMAL(10, 4) only allows for 4 numbers after the decimal.'
END CATCH

-- Here's one way to truncate the string to a castable value.
SELECT CAST(LEFT(@TestConvert, (CHARINDEX('.', @TestConvert, 1) + 4)) AS DECIMAL(14, 4))

-- If you noticed, I changed it to DECIMAL(14, 4) instead of DECIMAL(10, 4) That's because this number has 14 digits, as proven below.
-- Read this for a better explanation as to what precision, scale and length mean: http://msdn.microsoft.com/en-us/library/ms190476(v=sql.105).aspx
SELECT LEN(LEFT(@TestConvert, (CHARINDEX('.', @TestConvert, 1) + 4)))

- Jeremy Pridemore

Answer 3

我想出了以下解决方案：

SELECT [Str], DecimalParsed = CASE 
WHEN ISNUMERIC([Str]) = 1 AND CHARINDEX('.', [Str])=0 AND LEN(REPLACE(REPLACE([Str], '-', ''), '+', '')) < 29 THEN CONVERT(decimal(38,10), [Str])
WHEN ISNUMERIC([Str]) = 1 AND (CHARINDEX('.', [Str])!=0 AND CHARINDEX('.', REPLACE(REPLACE([Str], '-', ''), '+', ''))<=29) THEN 
    CONVERT(decimal(38,10), 
            CASE WHEN LEN([Str]) - LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Str], '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')) <= 38 
                 THEN [Str] 
                 ELSE SUBSTRING([Str], 1, 38 + LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Str], '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', ''))) END)
ELSE NULL END
FROM TestStrToDecimal

我知道这看起来可能有些过度，实际上也许是这样的，但是对我来说有效（已检查过不同精度和规模的正数、负数、大数和小数——所有内容都被转换为decimal(38,10)或NULL）。

它是硬编码为decimal(38,10)类型的，所以如果您需要不同的精度，请更改代码中的常量（38、10、29）。

它如何工作？结果是：

• 如果转换简单且没有溢出或精度损失（例如123或123.456），则只需将其转换即可。
• 如果数字不太大，但小数点后位数太多（例如123.1234567890123456789012345678901234567890），则将其截取在末尾多余的数字，只保留前38个数字。
• 如果数字太大，在不发生溢出的情况下无法转换为十进制（例如9876543210987654321098765432109876543210），则返回NULL。

每种情况在上面的代码中都是单独的WHEN语句。

以下是一些转换示例：

- Sasha

Answer 4

你还没有解释为什么不能使用浮点数据类型，所以这里有一个例子：

DECLARE @StringVal varchar(50)

SET @StringVal = '123456789.1234567'
SELECT @StringVal, CAST(@StringVal AS FLOAT)

SET @StringVal = '1.12345678'
SELECT @StringVal, CAST(@StringVal AS FLOAT)

SET @StringVal = '123456.1234'
SELECT @StringVal, CAST(@StringVal AS FLOAT)

- Mark Kram

---

我无法使用float或任何其他类型，因为目标列是Decimal(10,4)类型，而我无法修改它。 - Biju Thomas

Answer 5

您需要注意的是，6.999,50不是有效的十进制数。一个十进制数中不能同时有逗号和小数点，这个数字应该是什么？

假设您的语言环境将"."作为分组符号，","作为小数分隔符：要删除分组数字：

SELECT CONVERT(decimal(11,2), REPLACE('6.999,50', '.', ''))

将得到6999.50作为十进制数。

- user2714866

Answer 6

在将值放入该列之前，您需要自己将其作为字符串截断。

否则，如果您想要更多的小数位数，则需要更改十进制列的声明。

- dodexahedron

Answer 7

你所面临的主要问题不是小数点右侧的数字，而是左侧的数字。类型声明中的两个值是精度和标度。

根据MSDN的说明：“精度是数字中的位数，标度是数字小数点右侧的位数。例如，数字123.45的精度为5，标度为2。”

如果你指定(10, 4)，那意味着你只能存储小数点左侧最多6位数字，或最大为999999.9999的数字。任何大于这个范围的数字都会导致溢出。

- Bill

Answer 8

使用自定义函数实现。 这将检查字符串值是否可以安全地转换为十进制数。

CREATE FUNCTION [dbo].[TryParseAsDecimal]
(
    @Value      NVARCHAR(4000)
    ,@Precision INT
    ,@Scale     INT
)

RETURNS BIT
AS
BEGIN

    IF(ISNUMERIC(@Value) =0) BEGIN
        RETURN CAST(0 AS BIT)
    END
    SELECT @Value = REPLACE(@Value,',','') --Removes the comma

    --This function validates only the first part eg '1234567.8901111111'
    --It validates only the values before the '.' ie '1234567.'
    DECLARE @Index          INT
    DECLARE @Part1Length    INT 
    DECLARE @Part1          VARCHAR(4000)   

    SELECT @Index = CHARINDEX('.', @Value, 0)
    IF (@Index>0) BEGIN
        --If decimal places, extract the left part only and cast it to avoid leading zeros (eg.'0000000001' => '1')
        SELECT @Part1 =LEFT(@Value, @Index-1);
        SELECT @Part1=SUBSTRING(@Part1, PATINDEX('%[^0]%', @Part1+'.'), LEN(@Part1));
        SELECT @Part1Length = LEN(@Part1);
    END
    ELSE BEGIN
        SELECT @Part1 =CAST(@Value AS DECIMAL);
        SELECT @Part1Length= LEN(@Part1)
    END 

    IF (@Part1Length > (@Precision-@Scale)) BEGIN
        RETURN CAST(0 AS BIT)
    END

    RETURN CAST(1 AS BIT)

END

- Biju Thomas

---

你是救星！非常感谢。 :) - Annie Lagang

Answer 9

我知道这是一个老问题，但Bill似乎是唯一一个真正“解释”这个问题的人。其他人似乎都提出了针对声明误用的复杂解决方案。

“您类型声明中的两个值是精度和标度。”

...

“如果您指定（10,4），那意味着您只能在小数点左边存储6位数字，或者最大数字为999999.9999。任何比这更大的数字都会导致溢出。”

因此，如果您声明DECIMAL（10,4），则可以拥有 10 个数字，其中 4 个数字在小数点后面。 因此，123456.1234有10个数字，小数点后有4个数字。它将适合DECIMAL（10,4）的参数。 1234567.1234将抛出错误。有11个数字要放入10个数字的空间中，并且必须在小数点后使用4个数字。不能从小数的左侧削减一个数字。 如果您的11个字符是123456.12345，则不会引发错误，因为从小数值的末尾修整（四舍五入）是可接受的。

在声明小数时，始终尝试声明您的列实际上会使用的最大值以及您想看到的最大小数位数。 因此，如果您的列只会显示最多100万的值，并且您只关心前两个小数位，声明为DECIMAL（9,2）。 这将在抛出错误之前给您最大的9999999.99。

在尝试修复问题之前了解问题将确保您选择适合您情况的正确修复方法，并帮助您理解修复方法的原因/工作原理。

再次强调，我知道我晚了五年参加聚会。 但是，根据您的评论，该列已设置为DECIMAL（10,4）并且无法更改，对此的解决方案是最简单的方法。 检查您的小数是否不超过10个点，然后将其修剪为10个数字。

CASE WHEN CHARINDEX('.',CONVERT(VARCHAR(50),[columnName]))>10 THEN 'DealWithIt'
ELSE LEFT(CONVERT(VARCHAR(50),[columnName]),10) 
END AS [10PointDecimalString]

我将这个值保留为字符串，是因为你需要处理小数点左侧超过10位的值。但这只是一个开始。

- DDuffy

Answer 10

create function [Sistema].[fParseDecimal]
(
    @Valor nvarchar(4000)
)
returns decimal(18, 4) as begin
    declare @Valores table (Valor varchar(50));

    insert into @Valores values (@Valor);

    declare @Resultado decimal(18, 4) = (select top 1 
        cast('' as xml).value('sql:column("Valor") cast as xs:decimal ?', 'decimal(18, 4)')
    from @Valores);
    
    return @Resultado;
END

- Moises Conejo