Tricky Select statement - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Tricky Select statement

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-09-22 : 08:59:12

Guys

I have following column data varchar(20)

data
______
(1,2,3)
(1,2)
(1,4,6)

I want a query which calculates the sum of the column data in the above case
sum = 1+2+3+1+2+1+4+6 = 30

earlier I used something like this but it doesnt work since the length the string is not always the same

select cast (substring ('1,2', 1, 1) as int)+cast(substring ('1,2', 3, 1) as int)

Any suggestions/inputs would help

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 09:03:35

quote:
Originally posted by scelamko

sum = 1+2+3+1+2+1+4+6 = 30

sum = 1+2+3+1+2+1+4+6 = 20

declare @test table (item varchar(20))

insert	@test
select	'1,2,3' union all
select	'1,2' union all
select	'1,4,6'

declare @s varchar(8000)

select @s = 'SELECT 0'

select @s = @s + '+' + replace(item, ',', '+') from @test

exec (@s)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 09:21:02

If you don't like dynamic sql, use this

declare @test table (item varchar(20))

insert	@test
select	'1,2,3' union all
select	'1,2' union all
select	'1,4,6'

declare @sum int

SELECT		@sum = SUM(CAST(SUBSTRING(',' + t.item + ',', w.i + 1, CHARINDEX(',', ',' + t.item + ',', w.i + 1) - w.i - 1) AS INT))
FROM		@test t
CROSS JOIN	(
				SELECT	DISTINCT Number i
				FROM	master..spt_values
				WHERE	Number BETWEEN 1 AND 255
			) w
WHERE		w.i = CHARINDEX(',', ',' + t.item + ',', w.i)
			AND w.i < LEN(',' + t.item)

select @sum answer

Peter Larsson
Helsingborg, Sweden

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-09-22 : 09:45:40

Peso,

Thank you for your reply, but my table is dynamically growing and hence I am to use the combination of replace and char functions to replace the charecters in the rows.

right now I have 2480 rows in the table

any suggestions/inputs ?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 09:47:44

Yes, my second suggestion.
It will work as long as the individual records are not longer than 255 characters, including commas.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 09:49:05

Now I noticed that the length is never longer than 20 characters, sorry!

declare @test table (item varchar(20))

insert	@test
select	'1,2,3' union all
select	'1,2' union all
select	'1,4,6'

declare @sum int

SELECT		@sum = SUM(CAST(SUBSTRING(',' + t.item + ',', w.i + 1, CHARINDEX(',', ',' + t.item + ',', w.i + 1) - w.i - 1) AS INT))
FROM		@test t
CROSS JOIN	(
				SELECT	DISTINCT Number i
				FROM	master..spt_values
				WHERE	Number BETWEEN 1 AND 20
			) w
WHERE		w.i = CHARINDEX(',', ',' + t.item + ',', w.i)
			AND w.i < LEN(',' + t.item)

select @sum answer

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources