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

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: enter image description here


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

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(13, 4))

Should be like 9 int + 4 floating = 13 chars

Tags:

Sql

Sql Server

Sql Server 2008

Recent Posts