VARCHAR 到 DECIMAL [英] VARCHAR to DECIMAL

问题描述

我想将 varchar(max) 列转换为 decimal(10,4).

I want to convert a varchar(max) column to decimal(10,4).

当我尝试使用 cast 或 convert 时，出现算术溢出异常.问题是存储在 varchar 列中的数据可能包含不同的精度和不同的比例.例如，123456789.1234567'、1.12345678 或 123456.1234.

When I try to use cast or convert I am getting an arithmetic overflow exception. The issue is that the data stored in the varchar column may contain different precisions and different scales. For example, 123456789.1234567', 1.12345678 or 123456.1234.

对于像 123456.1234 这样的值，它转换没有任何问题，但对于其他值，我遇到了一些问题.

For values like 123456.1234 it is converting with out any issue but for other values I am having some problems.

推荐答案

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

Implemented using Custom Function. This will check whether the string value can be converted to Decimal safely

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

这篇关于VARCHAR 到 DECIMAL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！