Apaxe :

In the database i have a table with this information:

key_id =1

key_desc =43+34+22+12

I want sum the values in key_desc. Something like:

SELECT key_desc FROM table

But the result of the select was "111" and not "43+34+22+12".

Is this posible?

1. Assuming you have a table and insertions like following ones

(using T-SQL syntax):

CREATE TABLE Test(key_id int, key_desc varchar(100))

INSERT INTO Test SELECT 1, '43+34+22+12' UNION SELECT 2,'34+56+78'

2. Assuming you have a following sequenve view (again using T-SQL)

CREATE VIEW [dbo].[Sequence](seq)

AS

SELECT hundred * 100 + ten * 10 + unit + 1

FROM (SELECT 0 UNION SELECT 1 UNION

SELECT 2 UNION SELECT 3 UNION

SELECT 4 UNION SELECT 5 UNION

SELECT 6 UNION SELECT 7 UNION

SELECT 8 UNION SELECT 9) AS Units(unit)

CROSS JOIN

(SELECT 0 UNION SELECT 1 UNION

SELECT 2 UNION SELECT 3 UNION

SELECT 4 UNION SELECT 5 UNION

SELECT 6 UNION SELECT 7 UNION

SELECT 8 UNION SELECT 9) AS Tens(ten)

CROSS JOIN

(SELECT 0 UNION SELECT 1 UNION

SELECT 2 UNION SELECT 3 UNION

SELECT 4 UNION SELECT 5 UNION

SELECT 6 UNION SELECT 7 UNION

SELECT 8 UNION SELECT 9) AS Hundreds(hundred)

3. Assuming you have only '+' operation, you can use a select like

following

SELECT Parsed.key_id,

SUM(Parsed.key_vls) sums

FROM (SELECT T.key_id,

CAST (SUBSTRING ('+' + T.key_desc + '+', MAX(S1.seq +

1), (S2.seq - MAX(S1.seq + 1))) AS INTEGER)

FROM Test AS T,

Sequence AS S1,

Sequence AS S2

WHERE SUBSTRING ('+' + T.key_desc + '+', S1.seq, 1) = '+'

AND SUBSTRING ('+' + T.key_desc + '+', S2.seq, 1) = '+'

AND S1.seq < S2.seq

AND S2.seq <= LEN(T.key_desc) + 2

GROUP BY T.key_id, T.key_desc, S2.seq

) AS Parsed(key_id, key_vls)

GROUP BY Parsed.key_id

If you have more operators in your string you have to add to this

query additional checks.

Idea is got from the book "JOE CELKO'S SQL PROGRAMMING STYLE".

Hope this helps,

Andriy