|
|
Posted by andrijz on 07/10/07 08:45
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
Navigation:
[Reply to this message]
|