[sql] VARCHAR to DECIMAL

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

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.

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

Any help would be appreciated.

After testing I found that it was not the decimal place that was causing the problem, it was the precision (10)

This doesn't work: Arithmetic overflow error converting varchar to data type numeric.

DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594'

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

This worked

DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594'

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

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

Your major problem is not the stuff to the right of the decimal, it is the stuff to the left. The two values in your type declaration are precision and scale.

From MSDN: "Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2."

If you specify (10, 4), that means you can only store 6 digits to the left of the decimal, or a max number of 999999.9999. Anything bigger than that will cause an overflow.

You are missing the fact that 6.999,50 is not a valid decimal. You can't have a comma and a decimal point in a decimal value surely? What number is it supposed to be?

Assuming your locale specifies . as grouping and , as decimal separator: To remove the grouping digits:

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

will yield 6999,50 as a decimal

You still haven't explained why you can't use a Float data type, so here is an example:

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)

In case you need to ROUND the result, not truncate, can use this:

select convert(decimal(38,4), round(convert(decimal(38,10), '123456789.1234567'),4))

This will return the following:

'123456789.1235' for '123456789.1234567'
'123456789.1234' for '123456789.1234467'

My explanation is in the code. :)

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

You are going to have to truncate the values yourself as strings before you put them into that column.

Otherwise, if you want more decimal places, you will need to change your declaration of the decimal column.

I came up with the following solution:

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

I know it looks like an overkill and probably it is, but it works for me (checked both positive, negative, big and small numbers of different precision and scale - everything is converted to decimal(38,10) or NULL).

It is hard-coded to decimal(38,10) type, so if you need different precision, change the constants in the code (38, 10, 29).

How it works? The result is:

• if conversion is simple without overflow or precision loss (e.g. 123 or 123.456), then it just convert it.
• if number is not too big, but has too many digits after decimal point (e.g. 123.1234567890123456789012345678901234567890), then it trims the exceeding digits at the end keeping only 38 first digits.
• if number is too big and can't be converted to decimal without an overflow (e.g. 9876543210987654321098765432109876543210), then NULL is returned

each case is separate WHEN statement inthe code above.

Here are few examples of conversion:

In MySQL

select convert( if( listPrice REGEXP '^[0-9]+$', listPrice, '0' ), DECIMAL(15, 3) ) from MyProduct WHERE 1

I know this is an old question, but Bill seems to be the only one that has actually "Explained" the issue. Everyone else seems to be coming up with complex solutions to a misuse of a declaration.

"The two values in your type declaration are precision and scale."

...

"If you specify (10, 4), that means you can only store 6 digits to the left of the decimal, or a max number of 999999.9999. Anything bigger than that will cause an overflow."

So if you declare DECIMAL(10,4) you can have a total of 10 numbers, with 4 of them coming AFTER the decimal point. so 123456.1234 has 10 digits, 4 after the decimal point. That will fit into the parameters of DECIMAL(10,4). 1234567.1234 will throw an error. there are 11 digits to fit into a 10 digit space, and 4 digits MUST be used AFTER the decimal point. Trimming a digit off the left side of the decimal is not an option. If your 11 characters were 123456.12345, this would not throw an error as trimming(Rounding) from the end of a decimal value is acceptable.

When declaring decimals, always try to declare the maximum that your column will realistically use and the maximum number of decimal places you want to see. So if your column would only ever show values with a maximum of 1 million and you only care about the first two decimal places, declare as DECIMAL(9,2). This will give you a maximum number of 9,999,999.99 before an error is thrown.

Understanding the issue before you try to fix it, will ensure you choose the right fix for your situation, and help you to understand the reason why the fix is needed / works.

Again, i know i'm five years late to the party. However, my two cents on a solution for this, (judging by your comments that the column is already set as DECIMAL(10,4) and cant be changed) Easiest way to do it would be two steps. Check that your decimal is not further than 10 points away, then trim to 10 digits.

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

The reason i left this as a string is so you can deal with the values that are over 10 digits long on the left of the decimal.

But its a start.