Sum up varchar column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum up varchar column

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-06-22 : 03:38:26

Hai, how can i sum up the value in the varchar column

Following doesnt give me the expected result..
declare @var1 varchar(10)
set @var1 ='12345'
SELECT SUM(CAST(@var1 AS int)) AS [Total Salary]

help pls

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-22 : 03:43:59

what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 03:46:13

[code]declare @var1 varchar(10)
set @var1 ='12345'

SELECT SUM(1 * SUBSTRING(@var1, number, 1)) AS theSum
from master..spt_values
where type = 'P'
and number between 1 and len(@var1)[/code]

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-22 : 03:46:44

this ?


DECLARE @var1 varchar(10),
	@LEN	int
SET 	@var1 ='12345'
SELECT	@LEN = LEN(@var1)

SELECT 	SUM(CAST(SUBSTRING(@var1, NUMBER, 1) AS int)) AS [Total Salary]
FROM	F_TABLE_NUMBER_RANGE(1, @LEN)

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-22 : 03:47:06

bit too late

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 03:49:09

Does really typing "master..spt_values" take short time than typing "F_TABLE_NUMBER_RANGE"?

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-22 : 04:24:48

quote:
Originally posted by Peso

Does really typing "master..spt_values" take short time than typing "F_TABLE_NUMBER_RANGE"?

E 12°55'05.63"
N 56°04'39.26"

Blame on his coloring tool