Philips Tel Philips Tel - 1 year ago 81
PHP Question

Converting Varchar Value to Integer/Decimal Value in SQL Server

How to convert the value of records in a table with data type

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!

Answer Source

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


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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download