Sum Comma Seperated string

[sharmanic2002]

I have a table a which has a column x
x has values 12,34,56,78 .I need an sql query which can sum the CS values in that row like 12+34+56+78 and need to display the sum.Please advise me as I am desperate in searching it...
Thanks in Anticipation...

[galexyus]

You can use this UDF:

CREATE FUNCTION SumCSV(@CSV AS VARCHAR(1000))
RETURNS INT
AS
BEGIN

DECLARE @Sum INT, @CurNumber INT, @CommaIndex INT
SET @Sum = 0

DECLARE @CurNumStr VARCHAR(20)


WHILE LEN(@CSV) > 0
BEGIN

	SET @CommaIndex = CHARINDEX(',', @CSV)
	IF @CommaIndex = 0 SET @CommaIndex = LEN(@CSV)+1
	SET @CurNumStr = SUBSTRING(@CSV, 1, @CommaIndex-1)
	SET @CSV = SUBSTRING(@CSV, @CommaIndex+1, LEN(@CSV))
	IF ISNUMERIC(@CurNumStr)=1
	BEGIN
		SET @CurNumber = CONVERT(INT, @CurNumStr)
		SET @Sum = @Sum + @CurNumber
	END
END
RETURN @Sum
END

Then your query would be:

SELECT dbo.SumCSV(CSVC olumn)
FROM table

[dvroman]

Another Way Of Doing This Is To Create A Generic Function That Puts The Values In A Table and Sum The Resulting Table.

i.e. SELECT SUM(*) FROM dbo.ParseByComm a('1,2,3,4,5,6, 7,8')

/*************** *************** *************** *************** *****
**** Parse A Comma Delimited String Into A Table
*************** *************** *************** *************** *****/
CREATE FUNCTION dbo.ParseByComm a (
@String VARCHAR(600) )
RETURNS @TblSubString TABLE
(
VarSubString VARCHAR(10)
)
AS
BEGIN
DECLARE @intPos INT,
@SubStr VARCHAR(10)

-- Remove All Spaces
SET @String = REPLACE(@String , ' ','')
-- Find The First Comma
SET @IntPos = CHARINDEX(',', @String)
-- Loop Until There Is Nothing Left Of @String
WHILE @IntPos > 0
BEGIN
-- Extract The String
SET @SubStr = SUBSTRING(@Stri ng, 0, @IntPos)
-- Insert The String Into The Table
INSERT INTO @TblSubString (VarSubString) VALUES (@SubStr)
-- Remove The String & Comma Separator From The Original
SET @String = REPLACE(@String , @SubStr + ',', '')
-- Get The New Index To The String
SET @IntPos = CHARINDEX(',', @String)
END
-- Return The Last One
INSERT INTO @TblSubString (VarSubString) VALUES (@String)
RETURN
END