SQL - Sum content of cell

Question

I have a database with a table that very simplified looks like this

Column 1  Column 2 (varchar)
Vector 1  23^34^45^65
Vector 2  0^54^10^31

Now I want to sum the numbers in the cells of column 2 together. That is, I want it to look like this:

Column 1  Column 2 (varchar)
Vector 1  167
Vector 2  95

How do I do this in SQL?

Answer 1

Perhaps you want something like this:

SELECT a.col1, sum_of_values = SUM(d.val)
FROM (VALUES ('Vector 1', '23^34^45^65'), ('Vector 2', '0^54^10^31')) a (col1, col2)
CROSS APPLY (SELECT CONVERT(xml, '<a>' + REPLACE(a.col2, '^', '</a><a>')+'</a>')) AS b(doc)
CROSS APPLY b.doc.nodes('a') c (item)
CROSS APPLY (SELECT c.item.value('.', 'int')) d (val)
GROUP BY a.col1

Output:

col1     sum_of_values
-------- -------------
Vector 1 167
Vector 2 95

Explanation:

1. The VALUES clause is a placeholder for your data.
2. By REPLACE'ing caret (^) with XML tags we can use methods on the xml datatype to efficiently split values.
3. CROSS APPLY with the nodes() method of the xml datatype returns a new row per item, and an xml column containing the item value.
4. value('.', 'int') converts the inner text of an <a> element to an int.
5. The GROUP BY and SUM aggregate these results, and reduce the number of rows back to the original two.

Edit: You could move the split into its own function. Like this:

CREATE FUNCTION dbo.SplitIntOnCaret (@list varchar(max)) RETURNS TABLE AS RETURN
    SELECT Position = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    , Value = item.value('.', 'int')
    FROM (SELECT CONVERT(xml, '<a>' + REPLACE(@list, '^', '</a><a>')+'</a>')) AS a(doc)
    CROSS APPLY doc.nodes('a') c (item)

So your final query could look like this:

SELECT l.list_id, sum_of_values = SUM(s.value)
FROM dbo.MyLists l
CROSS APPLY dbo.SplitIntOnCaret(l.list) AS s
GROUP BY l.list_id

Answer 2

If you cannot change the way your data is stored, you will have to

1. Split the String (see Turning a Comma Separated string into individual rows)
2. Compute the SUM using GROUP BY.