Converting Varchar Value to Integer Decimal Value in SQL Server

Question

How to convert the value of records in a table with data type varchar to integer or decimal so I can get the sum of the value  I use SQL Server 2005   Say that I have table structure like this   ID int Stuff varchar   and the records  ID   1  Stuff   32 43 ID   2  Stuff   43 33 ID   3  Stuff   23 22   Now I want to get the total of column Stuff   SELECT SUM convert decimal 4 2   Stuff   as result FROM table   But it doesn t work  Please need help

User · Answer

The reason could be that the summation exceeded the required number of digits - 4. If you increase the size of the decimal to decimal(10,2), it should work

 SELECT SUM(convert(decimal(10,2), Stuff)) as result FROM table

OR

 SELECT SUM(CAST(Stuff AS decimal(6,2))) as result FROM table

User · Answer

Table structure   very basic   create table tabla ID int  Stuff varchar  50     insert into tabla values 1   32 43    insert into tabla values 2   43 33    insert into tabla values 3   23 22      Query   SELECT SUM cast Stuff as decimal 4 2    as result FROM tabla   Or  try this   SELECT SUM cast isnull Stuff 0  as decimal 12 2    as result FROM tabla   Working on SQLServer 2008

User · Answer

You are getting arithmetic overflow  this means you are trying to make a conversion impossible to be made  This error is thrown when you try to make a conversion and the destiny data type is not enough to convert the origin data  For example   If you try to convert 100 52 to decimal 4 2  you will get this error  The number 100 52 requires 5 positions and 2 of them are decimal    Try to change the decimal precision to something like 16 2 or higher  Try with few records first then use it to all your select

User · Answer

You can use it without casting such as   select sum  stuff   as mySum from test    Or cast it to decimal   select sum cast  stuff  as decimal 4 2    as mySum from test    SQLFiddle  EDIT  For SQL Server  you can use   select sum cast stuff as decimal 5 2    as mySum from test    SQLFiddle

[php] Converting Varchar Value to Integer/Decimal Value in SQL Server

Examples related to php

Examples related to sql

Examples related to sql-server