CODEWITHSUNDEEP
phpsqlsql-server
Plipus Tel
Plipus Tel

Reputation: 755

Converting Varchar Value to Integer/Decimal Value in SQL Server

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!

Upvotes: 5

Views: 107999

Answers (4)

Jose Areas
Jose Areas

Reputation: 719

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.

Upvotes: 2

Hackerman
Hackerman

Reputation: 12295

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

Upvotes: 8

Jon
Jon

Reputation: 4736

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

Upvotes: 0

codingbiz
codingbiz

Reputation: 26386

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

Upvotes: 1

Related Questions