VARCHAR to DECIMAL

Я хочу преобразовать столбец varchar(max) в decimal(10,4).

Когда я пытаюсь использовать cast или convert, я получаю исключение арифметического переполнения. Проблема заключается в том, что данные, хранящиеся в столбце varchar, могут содержать различные оценки и разные шкалы. Например, 123456789.1234567 ', 1.12345678 или 123456.1234.

Для таких значений, как 123456.1234, он преобразуется без какой-либо проблемы, но для других значений у меня возникают некоторые проблемы.

Любая помощь будет оценена.

Ответы

Ответ 1

Реализовано с использованием пользовательской функции. Это проверит, может ли строковое значение быть преобразовано в десятичное безопасно

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 > (@[email protected])) BEGIN
        RETURN CAST(0 AS BIT)
    END

    RETURN CAST(1 AS BIT)

END

Ответ 2

После тестирования я обнаружил, что причиной было не десятичное место, это была точность (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(18, 4))

Ответ 3

Мое объяснение содержится в коде.:)

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 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 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)))

Ответ 4

Вам не хватает того, что 6,999,50 не является допустимым десятичным. У вас не может быть запятая и десятичная точка в десятичном значении? Какое количество это должно быть?

Предположим, что ваш язык задан. как группировка и, как разделитель в десятичной форме: удалить цифры группировки:

SELECT CONVERT (десятичный (11,2), REPLACE ('6.999,50', '.', ''))

даст 6999,50 в виде десятичной дроби

Ответ 5

Вам придется обрезать значения самостоятельно как строки перед тем, как поместить их в этот столбец.

В противном случае, если вы хотите больше десятичных знаков, вам нужно будет изменить объявление десятичного столбца.

Ответ 6

Я придумал следующее решение:

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 в коде выше.

Вот несколько примеров преобразования:

Ответ 7

Вы все еще не объяснили, почему вы не можете использовать тип данных Float, так что вот пример:

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)

Ответ 8

Ваша основная проблема заключается не в том, что справа от десятичной дроби, а в левом. Два значения в объявлении вашего типа - это точность и масштаб.

Из MSDN: "Точность - это количество цифр в числе. число цифр справа от десятичной точки в числе. Например, число 123.45 имеет точность 5 и шкалу в 2 раза.

Если вы укажете (10, 4), это означает, что вы можете хранить только 6 цифр слева от десятичной суммы или максимальное число 999999.9999. Все, что больше, приведет к переполнению.

Ответ 9

Я знаю, что это старый вопрос, но Билл, похоже, единственный, кто фактически "разъяснил" проблему. Все остальные, похоже, придумывают сложные решения для злоупотребления декларацией.

"Два значения в объявлении вашего типа - это точность и масштаб."

...

"Если вы укажете (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, это не вызовет ошибки, поскольку обрезка (округление) с конца десятичного значения приемлема.

При объявлении десятичных знаков всегда пытайтесь объявить максимум, который будет использовать ваш столбец, и максимальное количество десятичных знаков, которое вы хотите увидеть. Поэтому, если в вашей колонке будут отображаться только значения, равные 1 миллиону, и вы будете заботиться только о первых двух десятичных знаках, объявите как DECIMAL(9,2). Это даст вам максимальное количество 9 999 999,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 цифр слева от десятичной дроби.

Но это начало.

Ответ 10

Если вам нужно округлить результат, а не усечь, можно использовать это:

выберите преобразование (десятичное число (38,4), округление (преобразование (десятичное число (38,10), '123456789.1234567'), 4))

Это возвращает: "123456789.1235" для "123456789.1234567" "123456789.1234" для "123456789.1234467"