Geek asks and answers

Convert Varchar value to integer / decimal value in SQL Server

How to convert the value of records into a table with a data type varcharin integeror decimalso that I can get the sum of the value. I use SQL Server 2005.

Let's say that I have a table structure as follows:

ID int
Stuff varchar

and records

ID = 1, Stuff = 32.43
ID = 2, Stuff = 43.33
ID = 3, Stuff = 23.22

Now I want to get the total number of columns:

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

But that will not work. Please, help!

+5
source share
4 answers

The table structure ... is very simple:

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 the following:

SELECT SUM(cast(isnull(Stuff,0) as decimal(12,2))) as result FROM tabla

Work with SQLServer 2008

+8
source

. , . , , . :

100.52 (4,2), . 100.52 5 2 .

16,2 . , .

+2

, - 4. decimal(10,2),

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


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

, :

select sum(`stuff`) as mySum from test;

:

select sum(cast(`stuff` as decimal(4,2))) as mySum from test;

SQLFiddle

SQL Server :

select sum(cast(stuff as decimal(5,2))) as mySum from test;

SQLFiddle

0

More articles:


All Articles