tags:

views:

301

answers:

2

There is a column in database which contains comma separated values like: 0.00,12.45,14.33 and so on.

I need to sum this inside a stored procedure. One way which I can think of is to split and convert it into a table using a function and then sum it.

Any other ideas?

+1  A: 

within a function you could try something like this, totally unsure if it will work tho!

CREATE FUNCTION ufn_sum_csv(@string varchar(100))
RETURNS @result int
AS BEGIN
EXEC 'SELECT @result = ' + REPLACE(@string,',','+')
RETURN

Can't try it out on this comp.

Paul Creasey
declare @int int; SELECT @int = REPLACE('0.1,11.23' ,',','+'). Gives error 'Conversion failed when converting the varchar value '0.1+11.23' to data type int.'
Aseem Gautam
Try changing the @result variable to FLOAT instead of INT
Sparky
This should do it (@result is a float):DECLARE @SQL NVARCHAR(500) = N'SELECT @result_out = ' + REPLACE(@string, ',', '+')EXECUTE sp_executesql @SQL, N'@result_out FLOAT OUTPUT', @result_out = @result OUTPUTSELECT @result
Alex K.
+2  A: 

Using Sql Server 2005+ CTE you can create a recursive select, something like

DECLARE @Table TABLE(
        ID INT,
        Vals VARCHAR(100)
)

INSERT INTO @Table SELECT 1, '0.00,12.45,14.33'
INSERT INTO @Table SELECT 2, '1,2,3,4'

;WITH ValList AS(
        SELECT  ID,
                CAST(LEFT(Vals,PATINDEX('%,%', Vals) - 1) AS FLOAT) Val,
                RIGHT(Vals,LEN(Vals) - PATINDEX('%,%', Vals)) Remainder
        FROM    @Table
        UNION ALL
        SELECT  ID,
                CAST(LEFT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN LEN(Remainder) ELSE PATINDEX('%,%', Remainder) - 1 END) AS FLOAT) Val,
                RIGHT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN 0 ELSE LEN(Remainder) - PATINDEX('%,%', Remainder) END) Remainder
        FROM    ValList
        WHERE   LEN(Remainder) > 0

)
SELECT  ID,
        SUM(Val)
FROM    ValList
GROUP BY ID

OUTPUT

ID          Total
----------- ----------------------
1           26.78
2           10
astander
Whew!!.. ok I am trying this out.. but its well above my IQ.
Aseem Gautam