有关字段累加
我现在有一个字段,数据类型是varchar(255),内容是 '82,65,32,98 ',也就是说,这个字段里面的数字是用 ", "隔开的,现在我想把这里面的数字取出来,并累加,想要得到它的累加值结果为 "277 ",不知该如何实现?

------解决方案--------------------
create table temp(aaa varchar(20))
declare @strsql as varchar(8000)
select @strsql= ' '
select @strsql= 'insert into temp values( ' ' '+replace( '82,65,32,98 ', ', ', ' ' ') insert into temp values ( ' ' ')+ ' ' ') '
exec (@strsql)
select sum(cast(aaa as int)) from temp
drop table temp

--结果
/*
277
*/
------解决方案--------------------
DECLARE @str varchar(1000)
SET @str = '82,65,32,98 '

DECLARE @re int
DECLARE @s nvarchar(4000)
SET @s = 'SET @re= ' + REPLACE(@str, ', ', '+ ')
EXEC sp_executesql @s, N '@re int OUT ', @re OUT
SELECT @re
------解决方案--------------------
--建立测试数据
CREATE TABLE A(id INT,string VARCHAR(100))
INSERT A
SELECT 1, '82,65,32,98 ' UNION ALL
SELECT 2, '10,20,30,40 '

SELECT * FROM A

--建立辅助临时表
SELECT TOP 8000 id = identity(int,1,1)
INTO #1 FROM syscolumns a, syscolumns b

--执行查询
SELECT A.ID, Num = CAST(SUBSTRING(A.string, B.ID, CHARINDEX( ', ', A.string + ', ', B.ID) - B.ID) AS INT)
INTO #2
FROM A, #1 B
WHERE SUBSTRING( ', ' + a.string, B.id, 1) = ', '
GO

SELECT ID,Total=SUM(CAST(Num AS INT))
FROM #2
GROUP BY ID

--删除测试环境
DROP TABLE A,#1,#2

--查看结果
/*
id string
1 82,65,32,98
2 10,20,30,40

ID Total
1 277
2 100
*/