VARCHAR to DECIMAL

Question

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

User · Answer

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

User · Answer

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

User · Answer

I came up with the following solution   SELECT  Str   DecimalParsed   CASE  WHEN ISNUMERIC  Str     1 AND CHARINDEX       Str   0 AND LEN REPLACE REPLACE  Str    -                   lt  29 THEN CONVERT decimal 38 10    Str   WHEN ISNUMERIC  Str     1 AND  CHARINDEX       Str    0 AND CHARINDEX      REPLACE REPLACE  Str    -                  lt  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         lt   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

User · Answer

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

User · Answer

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

User · Answer

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 gt 0  BEGIN         --If decimal places  extract the left part only and cast it to avoid leading zeros  eg  0000000001    gt   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  gt    Precision- Scale   BEGIN         RETURN CAST 0 AS BIT      END      RETURN CAST 1 AS BIT   END

User · Answer

In MySQL select convert  if  listPrice REGEXP    0-9      listPrice   0     DECIMAL 15  3    from MyProduct WHERE 1

User · Answer

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

User · Answer

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  quot Valor quot   cast as xs decimal      decimal 18  4        from  Valores            return  Resultado  END

User · Answer

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

User · Answer

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

User · Answer

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

[sql] VARCHAR to DECIMAL

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008